Form with data from database question

[Former Community Member]

I have just made a form so that I can test connecting to a database, and populate the form with the db data.

My form is very simple and I used MS Access for the db.
The db has two tables:

Table 1: ID, Wheels, Cost
Table 2: ID, Brakes, Cost

The form Hierarchy:

The form:

The Data View

The wheel and brake data flows into the ddlists from the db.

The cost values display the first costs in the table.

How do I "link" the cost value of the wheels to the wheel data, so that when you choose "wheel package 2"
from the ddlist, the cost value of wheel package 2 is displayed in the wheel cost field?

How can I sum the cost fields to provide a total?

I uploaded both the form and db here:  https://acrobat.com/#d=HEGvv-CMb0o5uPGxisU1Kw

[Former Community Member]

When I read this, I can see that I have not been very clear in describing my problem.

The "Wheels" ddlist is populated from the access db

The list items that appear are "Wheels Package 1" "Wheel Package 2" "Wheel Package 3" & "Wheel Package 4"

The cost value of these Wheel Packages are also populated from the access db.

How do I code this, so that when you select Wheel Package 1, in the ddlist, the cost value for Wheel Package 1 is displayed in the cost field.

When Wheel Package 2 is selected, the cost for Wheel Package 2 is displayed in the cost field, and so on...

Thanks for any help or advice.

[bpx_hmemcpy]

Anyone?

I have a feeling that I need to add somthing like this on the ddlist?

if (Len(Ltrim(Rtrim(SelectField.rawValue))) > 0) then
    //Change the commandType from TABLE to TEXT.  TEXT is the equivalent of SQL Property

    $sourceSet.DataConnection.#command.query.commandType = "text"

    //Set the Select Node.  Select in this case will be whatever the SQL Property you want. 
    $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat("Select * from Table1 Where ID = ", Ltrim(Rtrim(SelectField.rawValue)) ,"")

    //Reopen the Dataconnection 
    $sourceSet.DataConnection.open()
endif

I am really struggling with this, so any help will be greatly appreciated...

Thanks

[Srini_Dhulipall]

One way is to get the cost for each wheel into the dropdown. Means while you are specifying the Dynamic Properties for the dropdown, you have only used the Text property binding. You can use the Value property in the Dynamic Bindings and then get the Cost for each wheel.

In the change event, assign the Value property value to the TextField.

Second way is, to get all the Wheel types and Cost for each one into a 2-dimensional array at the renderign time itself and then display the data by processing the array values at runtime.

Hope this gives an idea..

Thanks

Srini

[Former Community Member]

Thanks for your repy Srini.

I am afraid that I am not following you...

If I was to follow your first method, does this mean I need to hardcode the cost values into the ddlists rather than have the costs flowing from the database?

Re, the second method - I have no idea how to do this? could this be a script object, and called from each dropdown list?

Thanks for your help.

[Former Community Member]

I am still struggling with this. Does anyone understand what Srini means?

thanks

[Former Community Member · Accepted solution]

You need to make two DB calls. The 1st one you have been able to do already that will populate the DDList with the appropriate values. Then a second call that will pass a SQL statement to the DB to retrieve the record that contains the value of the wheels or brakes that the user selected in the DDList. This part can only be done with code. There is a sample of how to do this at this location.

http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/

You may want to mine that site for other Form to DB information as he has a couple of different entries on DB access.

Paul

[Former Community Member]

Thanks Paul.