Insert multiple records from multiple lines of input from user

Queries and Resources
1

Hi ReTool Team! For starters, developing apps is NOT my full time job. I developed most of the apps that my team needs with queries, related tables and all CRUD functions operating. This last app is a little over my currently, so any guidance with this query and/or concept/flow of this app would be greatly appreciated.

This app "Assessment" has an initial job creation section (working fine). This initiates the job with basic info and creates the job id# (main table id).

assess_01
assess_011231×905 96.6 KB

The rest of the app is multiple inputs with data entered by the tech out in the field. These are broken down into categories (general, exterior, bedroom 01, etc). Each category will contain generally 10 separate line items (hopefully not more). Each line item data will be stored as a new record in a related table as outline in red in below screen shot.

assess_02a
assess_02a1226×1012 108 KB

If an item requires more details (Materials in this case as in the 'Vertical Blinds' line item, the data entered into that hidden container will be entered into a separate related table as it's own record).

I'm at a loss where to even begin with this. I did find this post by Chris-Thompson (Retool Team): https://docs.retool.com/docs/scripting-retool#triggering-a-query - I'm not quite comprehending this yet, but will continue to work with it if this is my best option.

Any guidance would be VERY helpful. Thank you in advance.

Mike Cash

2

@Mike_Cash Welcome to the forum!
There's a lot happening here and while I understand your position, without more information such as understanding the complete process step by step, it would be difficult to come up with a solution.
Some questions:

  1. Create assessment is done first by some one NOT in the field?
  2. When someone is IN the field, they select the assessment and then the categories (general, exterior, bedroom 01, etc) appear and they are then filled out.
  3. Within each of the categories (general, exterior, bedroom 01, etc), there are separate line items; is each line item a separate table in the database?
  4. Is Materials a separate table in the database as well?
  5. If 3 and 4 above are true, you can run a JS query to call each INSERT for each field(s) for each table.

If you are able to share more information( DB structure, etc.); please do.

3

Thank you for the clarifying questions.

General setup for assessment:
Main table: 'assess_jobs'
Related tables: 'assess_tasks' & 'assess_materials'

1.Yes, admin opens the job with 'Create Assessment' first.
2. When onsite, Tech/Mgr opens job (on ipad) and begins to fill out full assessment, category by category, entering only data specific to that job.
3. Yes, each line item, such as one outlined in red in pic above: 'Trash Out' (task, alt task, fix/replace, task details), are entered as one record in the related 'assess_tasks' table.
4. Each material (item, size, qty) would be one record in the related 'assess_materials' table.

Once the Assessment is completely filled out, tech hits 'submit', triggering all queries to run.
Preferably, only 'rows' with data would be entered and query would ignore any fields that are null.

Hopefully that helps and please feel free to ask any other questions.

Mike

4

Hi @Mike_Cash! Thanks for reaching out and sharing all of this context!

This sounds doable :blush: If it's SQL, this doc may be helpful

Is it a Postgres database? Are you using the Retool database?

I'm not 100% sure if I'm understanding where the blocker is, but I imagine something like this would work:

You can trigger an update query & then write some JS logic to only trigger the query if the inputs are not null

5

Hi Tess, thank you for your reply. My blocker here is my own inexperience. I have successfully created several apps tying into roughly 30 tables (related & stand alone) as well as 2 mobile apps that tie into the desktop apps. So I've got enough knowledge to be dangerous.

I am utilizing the Retool hosted Postgres DB.

My struggle is that the end user will not 'submit' data until all data has been entered, modified & checked. A button will be the trigger to begin queries. Data will consist of up to 10 rows of data in 10 different collapsible containers.

Do I just manually write a query for each row, with JS verification of null or not, and then have that successful query trigger the next query? Or is there a more efficient way of accomplishing this?

Again, apologies for my lack of experience and sincerely thank you for your time & assistance with this!

Mike

6

@Tess , @ScottR - I think I understand the issues @Mike_Cash is trying to solve (as I am having the same issue, and was looking for a solution) :smiley:

The issue is (I think)

Each assessment task, is a record in the same table (assess_tasks) - and he wants to add a record for each of the non-empty drop downs etc BUT..... relate them with the same 'Assessment ID' -

And so, the question is, how does one do this (who is not a dev).

Because, the GUI inserts etc, they all are for single records, or an array of records (which might be what is needed, but we do not know how to do this)...... So, it's how to take all the select boxes etc, and make them into one array, attach the 'Assessment ID' to this array, and then bulk insert.

That, I think, is what is being asked.

And if not - that is what I am asking - as I have a similar issue :smile:

I want to add 5 records, based on 5 select components - with one foreign-Key id, which is derived from a different select component

image
image1860×1148 115 KB

7

Hi @Mike_Cash! Apologies for the delay in circling back here

It sounds like you've already accomplished quite a bit in Retool! :sunglasses: I think the tricky (and powerful) part about Retool is that it is so customizable, so there's probably many ways to solve your case, and it's a little hard to say exactly what would be most efficient. However, here are some thoughts that come to mind as you're building.

You can "skip" queries when you have null submissions in a few ways. First, you can have success event handlers that only run when some condition. You can put Javascript in this field by wrapping it in {{}}.

You can also disable certain queries based on some Javascript condition in the advanced tab of the query:

You could also have some form validation that only lets a user submit the form when there are no nulls, although it sounds like nulls are OK for your use case. Our form docs go into more detail.

It sounds like the tricky part with your case, is handling the case of 'skip this record and move on to the next one'.

You could trigger queries on success of one another, but if a query is disabled or prevented from running based on null inputs, it won't trigger a success or failure event handler and the chain of queries will stop. One approach is that you could create lots of event handlers for every possible null submission.

Another approach could be to use the JS scripting method that you mentioned above. You could loop through each record, check if it needs to be submitted, and trigger the query if necessary. I haven't found a great example of this, but I did find this post which shows a different use case of triggering multiple queries from a single JS query

Another approach could be to create many success handlers (one for each record) that all trigger at the same time (when the form is submitted). With this approach, you could prevent certain queries from running for null submissions without having to worry about impacting other queries. Something to keep in mind here is that running too many queries at once can lead to performance issues.

Another thing to keep in mind is that the form doesn't inherently save the end user's work, so if they close or refresh the app before submitting, none of the queries will have run yet and they'll have to start over. Not sure if that matters for your use case!

8

Hi @maillme, Thanks for chiming in! Just seeing your post now :slightly_smiling_face:

9

For the case of a single bulk insert, you could create the array in a JS query & then trigger the database query and pass the array using additionalScope.

It would likely be a bit more complex, but here's an example JS query:

1 Like
10

To quote @ScottR "There is a lot going on here...". Indeed!

@maillme, you are pretty much on target with my issue. To clarify, each 'row' of inputs (selects or text), as laid out in the red highlight of the 2nd screen shot, would be one new record related to the Master wo_id. You are spot on with the "who is not a dev" aspect.

I have a ways to go to understand how I am to address my challenge. As for yours, if you only have 5 records to create, then @Tess 's single bulk insert is the way to go. Albeit not being a true dev, I don't comprehend it as of yet.

I personally would hard code it where Query one would insert one record with the key:value pairs being [foreign_key_column_name = maturity_index_2023_id.value, material_topic = selectInput1.value]. Duplicate this query 4 times, changing the 'material_topic' to reflect each consecutive select option from user. Add the 4 queries to the "Even Handlers Success" section. This should get you up and running (if I explained it well enough).

Hopefully my input helps and does not send you down a rabbit-hole wasting your time.

1 Like