Form is not entering data correctly

[tomm]

I have been trying to use a form to update data in the Retool database. I am using a date selector for the ship date. I have entered two records so far, both with a ship date of 12/5/2024. The first time, the record did get inserted, but the ship date was 12/12/2024. The second record also got inserted, but the ship date was 12/9/2024. Why is the ship date getting changed randomly before being added to the database?

Also, after submitting this, I went to enter another record. This time it kept the correct date, but entered the record over 30 times and duplicated another record I had already entered four times. Some of the addresses for the new record I entered had an address for another record I entered. I also got blank records with a different ship date then what I had selected. I am even getting additional records added when I refresh the data in the table using the refresh button in the footer. I don't think the form is resetting after submitting.

[MiguelOrtiz]

Hi @tomm ,

This is indeed weird and not the expected behavior. Are you able to share more about your general setup? It would be useful to have info and/or screenshots of:

• Whether you're using datasource for your form to prepopulate
• Whether you're using a variable to prepopulate your form
• What events are being triggered by your form upon submission
• Your insert query
• Any events being triggered by your insert query

Hopefully with the above we can troubleshoot

[tomm]

Hey @MiguelOrtiz, thanks for the reply. Here are the answers to your questions.

1. I am not using a datasource to prepopulate the form. The form is used only to enter new records.
2. I am not using a variable to prepopulate the form.
3. Once the form has been submitted, I try to trigger the confetti as a way of showing the user it was successful, and I trigger the query to refresh the table so they can see the record was added.
4. Here is the insert query:

INSERT INTO
  insurance (
    dealer_name
    notes,
    brand,
    dealer_state,
    new_da,
    dealer_email,
    liability_coverage_per_occurrence,
    liability_expiration_date,
    gl_status,
    auto_liability_coverage,
    auto_liability_expires,
    al_status,
    umbrella_liability_coverage,
    umbrella_liability_expires,
    workers_comp_coverage,
    workers_comp_expires,
    additional_insured,
    certificate_holder,
    insurance_company,
    insurance_agent,
    agent_email,
    pdf
  )
VALUES
  (
    {{form13.data.dealer_name}},
    {{form13.data.notes}},
    {{form13.data.brand}},
    {{form13.data.state}},
    {{form13.data.new_da}},
    {{form13.data.dealer_email}},
    {{form13.data.liability_coverage_per_occurrence}},
    {{form13.data.liability_expiration_date}},
    {{form13.data.gl_status}},
    {{form13.data.auto_liability_coverage}},
    {{form13.data.auto_liability_expires}},
    {{form13.data.al_status}},
    {{form13.data.umbrella_liability_coverage}},
    {{form13.data.umbrella_liability_expires}},
    {{form13.data.workers_comp_coverage}},
    {{form13.data.workers_comp_expires}},
    {{form13.data.additional_insured}},
    {{form13.data.certificate_holder}},
    {{form13.data.insurance_company}},
    {{form13.data.insurance_agent}},
    {{form13.data.agent_email}},
    {{form13.data.pdf}}
  )

5. I do not have any events triggered by the insert query, although I was wondering if I should have the confetti and the refresh query triggered after the insert is successful. The problem there is anything I put in the event handler for success never seem to fire.

Here is a screenshot of the database I am using:

Here is a screenshot of the page with the insert record form. As you can see, there are a number of fields that already have values in them, but I did not set it up that way. I want all the fields to clear once the form has been submitted.

Last but not least, here is a screenshot of the setting for the form.

I have been really struggling with this. I had to change my date fields to text fields because I cannot figure out how to insert a null value through the form, then I had all the other problems I listed above, so any help you could provide would be most appreciated.

Please let me know if you need any other information.

[MiguelOrtiz]

Hi @tomm,

This is very helpful, thanks for providing all the details!

I can see you are using {{ table21.selectedRow }} as datasource for your Form. This, connected to the fact that you have also the refresh query as an event for your form submission events may be what's causing the issue.

So basically what I think is happening is that:

1. You select a Row which pre-populates the form
2. You submit the form which triggers at the same time your refresh and insert queries. Potentially your refresh query updates your table, which could update your selected Row, thus updating the data in your form for a split second before the insert query finishes running and updating a completely different value

So, the approach I would take to troubleshoot this is:

1. Indeed, I would just leave 1 query in your form submission event handlers and add your confetti and refresh query triggered as success events from your insert query.
2. With regards to mapping the fields, for straightforward inserts like this I prefer using the GUI option as it allows you a field by field mapping (which in my opinion also makes it easier to the eye to apply any custom logic to a specific field, like the nulls you were mentioning).

The otheri ssue is why you've seen a record entered over 30 times, which suggest there may be a loop happening. You can righ click on your insert query and select "View state" and right at the bottom you'll see a section with all the connections for your query, in articular you want to see if it is controlled by something else than your form, see example:

I hope this helps troubleshoot!

[tomm]

I'm not sure how the source got set to {{ table21.selectedRow }}. That was not intentional. Should the data source be left blank then?

[MiguelOrtiz]

Yes, if this is for a new record completely and not prepopulating it from a selected Row.

[tomm]

One other question if I may. You mentioned using the GUI option to build the insert queries which would allow me to use custom logic to insert null values. Would you be able to explain exactly how to do that. I have seen it on here and I have tried, but it never works for me.

Also, from your detailed explanation of what you think is happening (thank you!) it sounds like I should have the refresh query be a event handler in the success for the insert query. Is that correct?

[MiguelOrtiz]

Hi @tomm,

With regards to custom logic, you can use something like:

{{ form13.data.umbrella_liability_expires === "" ? null : form13.data.umbrella_liability_expires }}

This checks whether your field is "", in which case it will return null, otherwise it will return the actual value in the field.

Not sure if this is the case you had in mind, but the logic applies to whatever condition you may want to apply.

Correct, you should have your refresh query to be set as an event handler in your insert query in case of success. I would actually add the confetti there as well.

[tomm]

Thank you so much @MiguelOrtiz. This has been a HUGE help and I appreciate it.

[MiguelOrtiz]

Not a problem, do let me know if you manage to solve the issue!

[tomm]

@MiguelOrtiz, I have implemented some of your suggestions and it seemed to work when I entered a record through the form, but after I submitted, a few of the fields are still showing data from the record I had just entered. Specifically, the model, the order date and the customer. Is there a way to make sure those all clear?

[MiguelOrtiz]

Hey @tomm,

Of course, yo ucan set up another event in your insert query success handlers which controls your form component as clear

[tomm]

@MiguelOrtiz thanks again.

I have noticed that when I click the move button that I added to a table that will insert the record into a new table and then delete from the old table, I can't get the query to run again to refresh the view. I added it as a event handler under success in the deletion query. Any idea what would prevent that?

As an example, I just created a form to enter pools that are built each day. I have three things that I want to trigger, the confetti, the query that creates the table so it shows the record just added and the clear component. The data entry was successful, but none of the three event handler success entries fired. I had to manually run the query again to see the new record and the form did not clear. I'm just trying to understand if I am entering something incorrectly or what.

[Tess]

Hi @tomm,

One thing to keep in mind is that if you have 3 events that all trigger on success of a single query, you won't be able to control the order of when the 3 events happen. If none of them are running, that is definitely unexpected

Some screenshots or a screen recording of the current state might help us sort out the issue, or if you prefer, you're welcome to join office hours.

[MiguelOrtiz]

Hey @tomm,

Sorry I got busy with other stuff and forgot getting back to you.

Whenever I have a series of sequential queries that need to have a specific order, I will use a script to ensure the logic is correct and that I don't have to create event handlers with conditions in my queries, an example would be:

on the button the triggers the first query add an event handler which is a script and in the script something like:

query1.trigger({
  onSuccess: query2.trigger({
    onSuccess: query3.trigger()
  })
});

Of course the logic can adapt to your needs with multiple actions, logs an errors as well

[tomm]

No worries @MiguelOrtiz. I appreciate all the help. I will try the script approach and see how it goes.

[tomm]

I tried this script:

move_new_orders_to_yard.trigger({
  onSuccess: delete_from_new_orders({
    onSuccess: new_orders_2024.trigger({
      onSuccess: rp_yard_fortville.trigger({
        onSuccess: utils.confetti()
      })  
    })
  })
});

and it looks like everything worked except for new_orders_2024.trigger, which I wanted to run to update the table and show the record had been moved.

[Tess]

It looks like you may need .trigger on line 2