Populate Expression based on a dropdown selection - InfoPath Dev
in

InfoPath Dev

Having trouble finding a blog or post that answers your question? Check out our Custom Search Page

Populate Expression based on a dropdown selection

Last post 01-17-2010 10:06 PM by texastwo. 21 replies.
Page 1 of 2 (22 items) 1 2 Next >
Sort Posts: Previous Next
  • 06-03-2009 07:23 AM

    Populate Expression based on a dropdown selection

    Hello,

    I would like to automatically populate an expression box based on a dropdown list selection.

    I have:

    1. An ID column
    2. Facility Column
    3. Cost Column

    What I would like to do is have the Facility column as the contents for the dropdown. I would like the expression box to be the 'cost column'. I am just unsure how to make this happen??

     

    Thanks

  • 06-03-2009 01:14 PM In reply to

    Re: Populate Expression based on a dropdown selection

    Where is your data source located? I'm not sure where you'll be pulling those three items from.

    Ernesto Machado
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™


  • 06-03-2009 01:16 PM In reply to

    Re: Populate Expression based on a dropdown selection

    Coming from SQL

  • 06-03-2009 04:08 PM In reply to

    Re: Populate Expression based on a dropdown selection

    You should use Facility as the Display field in your dropdown and ID as the Value field, then create an expression box with an XPath formula like this, replacing the bold underlined parts:

     xdXDocument:GetDOM("TestTable1")/dfs:myFields/dfs:dataFields/d:TestTable1[@ID = xdXDocument:get-DOM()/my:myFields/my:field1]/@Cost

    The first TestTable1 is the name of the data source.
    The second is the name of the repeating field in the secondary data source.
    ID is the ID field in the secondary data source.
    /my:myFields/my:field1 is the path to the field to which the dropdown is bound.
    Cost is the cost field in the secondary data source.

    Jimmy Rishe / Software Developer / InfoPath MVP
    Qdabra Software
  • 06-03-2009 06:48 PM In reply to

    Re: Populate Expression based on a dropdown selection

    Thanks for your response. Now I am really confused.

  • 06-04-2009 01:32 AM In reply to

    Re: Populate Expression based on a dropdown selection

    Hi mbrady1973,

    To implement the formula defined by Jimmy please do the following:

    Configure the drop down list:

    [1] Right mouse click on your dropdownlist and choose Drop-Down List Box Properties
    [2] In the Drop-Down Properties dialog box, under List box entries select Look up values from an external data source.
    [3] In the Drop-Down Properties dialog box, select your sql data source
    [4] In the Drop-Down Properties dialog box, click on the button next to Entries, and select the folder where your data is stored
    [5] In the Drop-Down Properties dialog box, click on the button next to Value, and select the ID column
    [6] In the Drop-Down Properties dialog box, click on the button next to Display name, and select the Facility column
    [7] Click OK on all dialog boxes.

    Configure the expression box:

    [8] Add an expression box to the design area
    [9] You will notice that the Insert Expression Box dialog box appears
    [10] In the Insert Expression Box dialog box, click on the fx button.
    [11] In the Insert Formula dialog box, enter the formula as defined by jimmy*
    [12] Click OK on all dialog boxes
    [13] Test your form

    * In the formula:

    - Replace the first occurance of TestTable1 with the name of your sql data source, this name can be found in the DataSource Task Pane
    - Replace the second occurance of TestTable1 with the name of the reapiting field in the sql datasource, this is the same as what you selected in the Entries field, in the drop down properties
    - Replace the @ID with the ID Column in your sql data source this can be done by clicking on the Insert Field or Group button in the Insert fomula dialog box
    - Replace the field1 in /my:myFields/my:field1 with the name of the drop down list field.
    - Replace the @Cost with the Cost Column in your sql data source this can be done by clicking on the Insert Field or Group button in the Insert fomula dialog box 

    Hope this helps

  • 06-04-2009 05:41 AM In reply to

    Re: Populate Expression based on a dropdown selection

    Wow....Thanks!

    Sorry to be such a pain, but this is slowly coming together. I have tried my best, only to still receive an error when verifying. Does anything stand out?


    1. (Here is my "stab-at-it")         xdXDocument:GetDOM("disposal matrix")/dfs:myFields/dfs:dataFields/d:Disposal_Matrix[@Disposal_ID = xdXDocument:get-DOM()/my:myFields/@Disposal_Site]/@Disposal_Cost

    2. Error on Verification-              Function 'GetDOM' did not return a value, or it returned a value that cannot be converted to an XSL data type.

     

    Thanks again for your help!!!!

  • 06-04-2009 06:20 AM In reply to

    Re: Populate Expression based on a dropdown selection

    Could you post screenshots of both your Main Data Source and Secondary Data Source, so I can verify the formula?

    Please Expand all nodes when taking the screenshot

  • 06-04-2009 07:15 AM In reply to

    Re: Populate Expression based on a dropdown selection

    I cannot figure out how to post a screenshot in this forum....Boy, am I helpless!!!

  • 06-05-2009 01:05 AM In reply to

    Re: Populate Expression based on a dropdown selection

    Hi Mark,

    After having a look at your screenshots, the formula should be:

    xdXDocument:GetDOM("Disposal_Matrix")/dfs:myFields/dfs:dataFields/d:Disposal_Matrix[@Disposal_ID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:Roll_off_Zones/@Disposal_Site]/@Disposal_Cost

    Hope it helps

  • 08-28-2009 11:34 AM In reply to

    • bella
    • Not Ranked
    • Joined on 08-01-2009
    • Posts 9

    Re: Populate Expression based on a dropdown selection

    Cant find how to delete my initial post, I managed to figure out the formula on my own. 

    thanks,

    bella

  • 12-10-2009 06:49 PM In reply to

    Re: Populate Expression based on a dropdown selection

    Thanks Jimmy.

    I've been stuck on this for hours ! I have a repeating table with 3 columns like this:

    Job ID Job Description Apply
    00010002
    00010002
    Apply to more positions

    The jobs are stored in a secondary data source. The desired functionality is when the user selects a Job ID in the first column, the textarea in the second column shows the description of the job.

    Using Jimmy's post, I was able to bind an expression field to the Job Description by way of adding an extra field to the main data source, etc.... etc.... but I know there has to be an easier way than my implementation. How to tie the description box to the description field of the element selected by the ID field of that row ?

     This isn't to hijack the thread but any advice on how this works will be greatly appreciated !

    The secondary data source looks like this:

     <Positions>

     <Position JobId="0001" JobDescription="Do some work"/>

    <Position JobId="0002" JobDescription="Do some other work"/> 

    </Positions>

  • 12-10-2009 07:05 PM In reply to

    Re: Populate Expression based on a dropdown selection

    It sounds like you should be using an Expression Box to show the description instead of a Text Box. This will save you from adding a field to your main data source to hold the value. Since your data is repeating, you'll also need to use the current() function when setting up your expression, but it sounds like you may have figured that out already.  If you get stuck on that part, attach screenshots of your main and secondary data source, and I can show you how to construct the function.
    Jimmy Rishe / Software Developer / InfoPath MVP
    Qdabra Software
  • 12-10-2009 07:09 PM In reply to

    Re: Populate Expression based on a dropdown selection

    In a repeating table, if I used an Expression field in the 2nd column, how would I reference the ID selected in the 1st column ?

  • 12-10-2009 07:35 PM In reply to

    Re: Populate Expression based on a dropdown selection

    You should be able to use current()/my:ID (or whatever the field is called) to reference the ID in the same row as the expression box.
    Jimmy Rishe / Software Developer / InfoPath MVP
    Qdabra Software
Page 1 of 2 (22 items) 1 2 Next >
Copyright © 2003-2012 Qdabra Software. All rights reserved.
View our Terms of Use.