Jump to content
  • 0

Autofill cell based on selected options in your submission form.


NickL

Question

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 Screenshot.png.5d1b8edc373a05959556fd910b0b0ce3.png 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. 

Screenshot 2023-07-25 120829.png

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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:
CF3uoiR.png


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/
iFiJMru.png

The result:
E08QaGw.png

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]

D739DBc.png

 

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:
wdpQDx4.png


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])))

RtAyr6a.png

 

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

 

Link to comment
Share on other sites

  • 0

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? 
pkrwB8y.png
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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...