Jump to content
  • 0

Get A Value With Sql In Submission Form


Parma2015
 Share

Question

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

Link to comment
Share on other sites

13 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

  • 0

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..:-/

Link to comment
Share on other sites

  • 0

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?

Link to comment
Share on other sites

  • 0

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?

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

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?

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

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

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...
 Share

×
×
  • Create New...