Create Multiple Submissions to a Table Based on User Selection of Unique Records

[wimtracking2]

I have three tables:

• tbl_sites (table with names and locations of sites)
• tbl_programs (table with name of programs)
• tbl_site_to_program (table of record ID associations made between the tbl_sites (site_ID) and tbl_programs (program_ID))

I currently create a submission to tbl_site_to_program by using a dropdown list of tbl_sites (site_ID) and by pulling in the tbl_programs (program_ID) as a parameter. This allows me to create one association into tbl_site_to_program at a time. However, I would like to be able to make many associations (submissions to tbl_site_to_program) at once based on multiple selections made using the dropdown list of tbl_sites (site_ID). Is there a way to select multiple records from the dropdown list of tbl_sites (site_ID) and create new records in tbl_site_to_program OR is there a way to use a grid style datapage to accomplish this? See image Site to program association to see the current datapage used (one site can currently be selected from the "Select from Existing Site List" dropdown).

The Caspio user interface has a similar functionality using a grid style sheet. See attached image Caspio UI Screenshot.png. User can select multiple datapages at once and complete an action such as move all selected datapages to a new folder.

[Volomeister]

Hi @wimtracking2

Here is a high overview of what I think can be done:

1. You create an auxiliary table for the initial submission. This table will have at least 2 fields: 
1.1 Field that will store multiple comma-separated site_IDs (text255)
1.2 Field that stores  program_ID
2. You create a triggered action for auxiliary table:
2.1. Trigger runs on insert action
2.2 Trigger loops through each comma-separated value in 1.1. field and inserts corresponding records in tbl_site_to_program table
3. You create a form DataPage based on the auxiliary table.
3.1 Configure it so it receives program_ID parameter as you do in your current page
3.2 Turn "associate a site to this program" into multi-select drop-down by adding multiple attribute to this dropdown using JavaScript, e.g. document.querySelector('#InsertRecordsite_ID').setAttribute('multiple', true) 
Selected options will be submitted as comma-separated site_IDs

So in this flow, user will be able to select multiple options from the drop-down. These options will be saved into auxiliary table along with program_ID. Triggered action will populate tbl_site_to_program table based on submitted data.

[wimtracking2]

@Volomeister Thank you. I was able to set up this workflow as you outlined. I have the datapage and triggered action working. The only hurdle is I ended up using a listbox, rather than a dropdown in the datapage. I would prefer to use a dropdown field, however I was not able to get the JavaScript (below) to work on a dropdown field. Do you have any suggestions?

<SCRIPT LANGUAGE="JavaScript">

document.addEventListener('DataPageReady', assignMultiple)

function assignMultiple() {
document.querySelector('#InsertRecordMultiple_Site_IDs').setAttribute('multiple', true);
}
</SCRIPT>

Triggered Action: For anyone interested in the triggered action used, see screenshot attached and link to post I referenced. I added TRIM to the field extracted from the comma delimited values.

 

 

[Volomeister]

Hello @wimtracking2

Using drop-down form element for text_255 field will work and the script you share seems to be working on my end. I would suggest double-checking the ID of this drop-down to make sure it is the correct ID