Autofill cell based on selected options in your submission form.

[NickL]

Hi,

I've been working on this for a while now trying to figure this out. Please see photos below. I hopefully this will help give a little visual of what I'm trying to accomplish. 

Photo 1 is my table and photo #2 is my submission form. 

Here is what i need help with. I want to setup an automation where if I select any of the option from the submission form, and based on what i select the price will be added to an empty cell under my "Collection Unit Charges" in my Table. 

so far i've been manually adding the numbers in my table under Collection Unit Charges. 

If i select from my submission form  option 1 "Smoking collectable $400 I want then 400 to be added to my table under "Collection Unit Charges" again i've been adding these manually so far. 

[CoopperBackpack]

Hello @NickL,

I don`t know where the 'Collection Unit Charges' value is stored. I assume that you have a Lookup table. If not you may create one in case each 'Collection_Unit' has a fixed 'charge'.
For example:



Then there are several options to get the 'charge'.

Option 1. Cascading elements.

On the Submission form, you may select the 'Cascading Text' form element for the 'Charge' field. Or another type of Cascading element.
This article can be helpful: https://howto.caspio.com/datapages/datapage-components/cascading-elements/


The result:

Option 2. Calculated Value.

It is always possible to retrieve data stored in the tables or views by using SELECT statements.

These articles can be helpful: 
https://howto.caspio.com/datapages/datapage-components/calculated-values/
https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

Formula example: 
 

SELECT Collection_Unit_Charges FROM Collection_units WHERE Collection_Unit = target.[@field:Collection_Unit]

 

Option 3. Triggered Action

Also, the Triggered Action can be used to populate the field (if this feature is included in your plan).

Trigger example:



Actually, according to the table design best practices, there is no need to store the same charge multiple times. 
Usually, it is enough to store an ID of the unique collection unit. And all the data about the collection unit is stored in a separate table. 

However, it depends on your workflow.

If you are looking for another solution and just need to get the number after the '$' sign, then a formula should be used.

TRY_CONVERT(float, SUBSTRING([@field:Collection_Unit],CHARINDEX('$', [@field:Collection_Unit]) + 1, LEN([@field:Collection_Unit])))

 

Feel free to update this thread if you have further questions. 

 

[NickL]

Hello @CoopperBackpack,

Thank you so much for your response. I do have a Chart and Report where all the Datas are sent to. this is my submission form. Friendly Fresh Hertz/Dollar/Thrifty (caspio.com) The "Collection Unit Charges" field is not part of the submission form. 

[NickL]

Hi @CoopperBackpack,

I'm getting this response

Invalid formula: Subqueries are not allowed in this context. Only scalar expressions are allowed.

using this Formula SELECT Collection_Unit_Charges FROM Collection_units WHERE Collection_Unit = target.[@field:Collection_Unit]

[CoopperBackpack]

Hello @NickL,

It looks like you use this statement in the Formula field in the Table.

If so, please note that SELECT statements are not supported in the Formula field.

I suggested this solution for the DataPage. 

I am afraid workflow details are still unclear to me. Do you only have these 4 options to select? 

What is the goal of storing the charge value in the table? If you need to calculate the total value it can be done in a different way.