Get A Value With Sql In Submission Form

[Parma2015]

Hi!

I have a Submission Form where truck drivers register each transport made. The transport could be i.e. sand, soil or stone. When they register an order, the article (sand, soil, stone) is determined, but the driver has to choose a vendor (from where he/she makes the transport). Each article has a different UnitPrice from different vendors. Therefore, I need to pull the UnitPrice from a table (VendArtLnk) when the vendor is selected (VendID).

In a calculated field in a report, I have managed to pull this value by SQL. The SQL is like this:

 

SELECT UnitPrice FROM VendArtLnk WHERE VendArtLnk.VendID = target[@field:IntOrders_ArtID AND VendArtLnk.ArtID = target.[@field:IntOrders_ArtID

 

In a subission Form, it's not possible to use calculated fields, but I was hoping I could do this through SQL inside a Javascript and then assign the value to a variable.

The next would be to insert the value into a field called UnitPrice.

The function should bi invoked by a cange in VendID.

 

Has anyone done something similar og know the exact code for this? I also need to know where to put it (in the header, footer or in a HTML Block)

Jan

[Jan]

Hello Jan,

 

Maybe you can use Cascading elements?

 

For example, the first field (that contains "sand", "soil" or "stone") is dropdown.

Vendors is a Cascading Dropdown, that displays vendors for the selected item of the first dropdown.

UnitPrice is a Cascading Text Field, that displays price for the selected vendor ID. You can check the "Make field non-editable when filter finds only one match" checkbox for this field, then a user sees the price but cannot change it.

 

I hope, it helps.

Please feel free to ask questions if anything is unclear.

[Parma2015]

Hi!

Thanks for replying! I tried to do that, but I think it's difficult or impossible to do it that way.

The thing is, I have decided the article through a cascading text field. The VendID is inside a variable, so I am pretty sure the best solution would be to pull it through a sql and then assign the value to Unit Price.

Seems I have to buy a coulpe of expert hours from Caspio to achieve it..:-/

[Jan]

Hi Jan,

 

Maybe, it is possible with Standard Features.

Sorry, I do not understand what means the "The VendID is inside a variable"

If I understand correctly, there is a table of Vendors, where every Vendor his own VendID, or VendID is stored by another way?

[aam82]

 

 

In a subission Form, it's not possible to use calculated fields, but I was hoping I could do this through SQL inside a Javascript and then assign the value to a variable.

 

 

is this possible?!

[Parma2015]

Hi!

The thing is that I want to define a value and then assign that value to a field in a submision form.

However, the value is dependent on selected values in one ore more dropdowns in the same form.

for instance: You select a vendor with the vendId = 8 a article with a ArtID = 13 and a Unit with a UnitID = 2

You want the SQL to look something like this:

SELECT FROM VendArtLnk UnitPrice WHERE VendID = [field@VendID] AND ArtID = [field@ArtID] And UnitID = [field@UnitID]

So my question is really wether it's possible or not to use SQL to pull a value from a table and then assign the value to a field in a submission form.

I have already done it in a calculated field in reports through a calculated field. What about submission forms?

[Parma2015]

I really hope so. se my answer :-)

[aam82]

you are trying to display UnitPrice from a table "vendartlnk" that has prices. Does this table have an autonumber/id field?

 

If so, use cascading dropdowns.

 

vendor dropdown

article cascading dropdown based on vendor, filtering articles by vendor, displaying article value.

unit price cascading dropdown based on article, filtering unit price by article, displaying unit price.

 

If not, then I suggest adding one. This way you don't need to have a conditional query every time you need to find a unique record from that table.

 

All dropdowns can be set to disabled if just one record is found. You can use cascading text on the last field if that's less confusing to your users.

[Parma2015]

Hi!

Thanks for your reply!

The solution you suggest is possible, but I need to find the UnitPrice without that the user have to select anything. I have first defined an order in which the vendor, article and Unit are also defined. The UnitPrice is in the table VendArtLnk where different vendors can have the same article (e.g. sand) and the same unit (e.g tons or m3), but with different UnitPrice.

Then the user will select an order and assign a transport to that order. In the submission form for registering transport (orderline), I want to pull the unitPrice based on article, unit and vendor. Since these three are already defined, I want to pull the value automaticly. If anyone has an answer to that I will be very, very happy :-)

[aam82]

Since these three are already defined, I want to pull the value automaticly.

 

So are these three being defined in this form, or were they defined previously, and now you are on a second form?

 

If in this form, then use my suggestion above.

 

If previously, then are you passing parameters to a second form?

[Parma2015]

Hi!

It's defined in a previous form and stored in table IntOrders.

The form in question receives parameters for article, unit and vendor.

To make it even more complicated, it's possible to change vendor on a single transport. If the yuser changes vendor, I need to select UnitPrice from table VendArtLnk based on the deined article and unit, and also the vendor (default or new).

I have a person helping me, and may be he found a way to do this through REST API.

He's working with it right now, and I will post a solution here if we succeed :-)

[aam82]

Please do. I'd be very interested to hear as much detail as you care to share about using the api.

[cheonsa]

Hi all,

 

This year, Caspio released new features. One of these features is Calculated Values in Submission Form. The Calculated Value form element generates dynamic calculations that automatically update as users interact with your form. SELECT statement is valid using this form element. 

You can find more information through this link: https://howto.caspio.com/datapages/datapage-components/calculated-values/

 

Hope this helps.

 

Regards,

kristina

[JolliBeng]

Hi all,

Just to add more information about the Calculated Values: As of Dec. 6, 2016, 

• Calculated Values can use other Calculated Values in Submission Forms.

You may want to check Caspio Bridge 16.0 to know more details about the current release.

 

-JolliBeng

[RuisiHansamu]

Here is the link that helped me learn about SQL example and applied to Caspio configuration: https://kb.iu.edu/d/ahux#:~:text=The syntax is%3A,a space between column names.