Pass Parameter From A Lookup Table Into Current Form Based On Cascading Dropdown Selection

[brbbrid]

I have 2 forms, 2 tables - one where user submits creative, and they're done then the other where they submit reimbursement for a previously submitted creative.  Since they can only submit reimbursement for a creative submission they've already submitted, I am using a cascading dropdown based on submission type (Advertising or Sales Contest) which is the same in the creative table/form.  They have to choose submission type then a name, start and end date in the creative form.  So for this reimbursement form, I have a cascading dropdown looking up the creative table via user unique id, and then based on submission type cascading dropdown, I have them go to the next cascading dropdown which shows the available previous submissions under that category.  

 

My question is, when they select the creative, I have a unique id for each creative submission in the other table/form that is an autonumber, is there any way for me to pass that unique creative id from that table into somewhere in the reimbursement form/table so that the reimbursement would be tied to the creative submission they previously did?  

 

Please help!  (Must figure this out sooner rather than later - I'm in a jam)

[Barbara]

It seems like a one to many relational database where one creative can have many reimbursements and you need to stamp the reimbursement with the creative id in the reimbursement table as the foreign key.

 

You can either achieve this by choosing the value of your dropdown as the ID field (there are Display and Value options in each dropdown that can be set separately for example Display to show the name of the creative and Value to store the id of the creative in the table).

 

Another totally different approach is what is explained in this video: http://howto.caspio.com/getting-started/create-a-one-to-many-relationship/

 

Cheers,

Barbara

[brbbrid]

Thanks Barbara, is there a way for me to also pass the name of the creative chosen as well?  The reason I ask is that I have a automatic email going to the sales rep and a notification email going to the program administrator upon submit that pulls in the information filled out in the form - serving as a record of notifying both entities of the reimbursement being submitted, so how would I also be able to store the creative submission name somewhere else in the form, maybe a virtual field or something, to be pulled into the emails so that the rep and admin will see the name of the creative submission that the user is submitting reimbursement for?

 

I'm not sure what the other approach would be as I am unable to use authentication - I'm not allowed to have a user name and password for the users to have to remember, so I'm currently having them look up their user record based on 2 different codes, their branch code and their zip code, which are unique enough together to identify them before moving forward into either form - and then pulling in all the pre-loaded contact/user info into both creative and reimbursement forms/tables - I do have a unique id for the user, but is one that I had to come up with outside of caspio and assign in excel prior to uploading all the users.  Is there a different way to accomplish what is in the video for one-to-many relationship without authentication?  Or another way to stamp the creative id into the reimbursement table as a foreign key?

 

Again, thank you so much for your insight...hoping you will be able to further help me out here 

[brbbrid]

OK, I sort of figured out a way to do this, but it only gets me halfway there.  I'm using another cascading dropdown but hiding it with HTML blocks so that it happens behind the scenes so to speak.  It's grabbing the Creative ID...but the problem I'm running into is that I had 2 cascading dropdowns looking up either Advertising or Sales Contest for the creative submission and was showing/hiding them based on the first cascading dropdown asking for the type of submission (same lookup table).  So now, I've combined them into one, but need to change the field label conditionally - so that if the 1st dropdown where they select "submission type" and it's either gonna be Advertising or Sales Contest, the 2nd dropdown where it looks up the name of the creative submission will say "Select Advertising submission" or "Select Sales Contest submission" based on that 1st dropdown selection....is there a way to accomplish this for field labels?  I opened another question here: http://forums.caspio.com/index.php/topic/4777-change-field-label-conditionally/?p=15014  

 

...since it is kind of a different topic.  But wanted to share with anyone else that the way to get around this if you don't have authentication like me, was to hide a cascading dropdown where you want a hidden value not seen by user, but passed nonetheless in the form or into an automatic email, like in my situation. 

 

I know that changing the field labels conditionally may seem silly, but trust me the people I'm building this for get hung up the font size, and things like that rather than functionality.  So the field label changing is a big deal to them.  If there is any way to accomplish this maybe with a javascript or something?  

 

Thank you in advance to anyone able to help!!!

[Barbara]

An idea came to my mind which is worth to try:

 

Disable the field label in the advanced tab

Insert an HTML block before the field and enable "next element on the same line" in the advanced tab. This way the content of the HTML block appears before the field on the same line.

Put the custom label in the HTML block like: Select [@InsertRecordFIELDNAME] submission

Replace FIELDNAME with your submission type field name.

Format the text the way you want it to display using HTML and Style tags.

 

I think it should work but I haven't tried it myself!

[brbbrid]

Hmmm...unfortunately its not pulling in the field... It's just showing "Select submission"....and the alignment was a bit off with all the other fields, although with time and some HTML styling, I'm sure I could make it work.  Just wish it would have pulled it in.  Perhaps it's because it's basing it off of a cascading dropdown?  But thank you so much for the suggestion!  I knew this was a tricky one 

 

If you have any other suggestions, I'm all ears 

 

Thanks again for the quick response!