Expand my Community achievements bar.

SOLVED

Form with data from database question

Avatar

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:

Snap1.jpg

The form:

Snap2.jpg

The Data View

Snap3.jpg

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

1 Accepted Solution

Avatar

Correct answer by
Former Community Member

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

View solution in original post

7 Replies

Avatar

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.

Avatar

Level 3

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

Avatar

Level 10

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

Avatar

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.

Avatar

Former Community Member

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

thanks

Avatar

Correct answer by
Former Community Member

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

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----