Cascading Drop-down Value not Auto-populating Additional Fields - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Cascading Drop-down Value not Auto-populating Additional Fields

Last post 11-15-2018 07:54 AM by Jacob Russ. 30 replies.
Page 1 of 3 (31 items) 1 2 3 Next >
Sort Posts: Previous Next
  • 09-11-2018 09:36 AM

    Cascading Drop-down Value not Auto-populating Additional Fields

    I have an InfoPath 2013 browser form that I generated from a SharePoint 2013 list.  In it I am utilizing cascading drop-downs for Region, Country, and State.  So when somebody selects a region (AP, EMEA, LA, or NA), the list of subsequent countries that are available for selection in the Country drop-down contains only relevant country values for that region.  Same deal with State.  These are both working great - I created a Region List, then a Country List that contains a lookup column from the Region list, and then a State list that contains a lookup column for the Country list.  I brought these in as secondary connections and added a Region filter to the Country drop-down, and a Country filter to the State drop-down.  Works like a charm.

    The problem is when I want to create more than one rule for auto-populating a few additional fields based on the value in State.  Currently, when a State value is selected from the drop-down, values are populating in 3 different fields, using a single rule.  But as soon as I add a second rule for another State value, I run into the following trouble:

    1. When "Don't run remaining rules if the condition of this rule is met" is not ticked for either rule, then selecting either one of the State values in the form will invoke Rule 2 and the 3 fields will end up being populated with the values specified in Rule 2.

    2. If "Don't run..." is ticked for Rule 1 and not for Rule 2, then the Rule 1 State populates the fields with the Rule 1 values, which is correct and what we want, and the Rule 2 State also populates the fields with the Rule 1 State values, which is what we don't want.

    3. If it's ticked for Rule 2 and not for Rule 1, then the fields all get the Rule 2 values, like #1 above.

    4. If it's ticked for both rules, then the fields all get the Rule 1 values, like #2 above.

    Another piece of info...I can't get the values in the State field itself to trigger these rules for the 3 auto-populating fields, so I am (very unfortunately) adding these rules to a Yes/No checkbox.  If you'll recall, the State field is part of a 3-tier Cascading Drop-down relationship.  Uncertain if this is relevant or not.

    Here is the logic for the 2 rules:

    Condition:
    [Checkbox] = "TRUE" OR [State] contains "State" (AND doesn't work here for some reason)

    Please note that I am unable to use "=" (instead of "contains") and "Type text..." here for the value, since this is a drop-down value and it is being seen as an integer I believe (ID?).  With an operator of "=", I only have these options: "Type a number...", "Select a field or group...", and "Use a formula...".  I am, however, able to instead refer to the original State fields in the data connection (as query fields or as data fields) and take advantage of the "=" operator along with a text string for the value.  The behavior of the 2 rules is exactly the same as above.  Again, uncertain if any of this is helpful, however.


    Rule Type:
    Action

    Run these actions:*
    Action: Set a field's value; Field: Autopop1; Value: Value
    Action: Set a field's value; Field: Autopop2; Value: Value
    Action: Set a field's value; Field: Autopop3; Value: Value

    What am I doing wrong?  Does anybody have any ideas?  Thanks in advance-


    -Jake

     

     

    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-12-2018 04:51 PM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    Any chance you can attach a copy of your form to a reply under the options tab? I have an easier time seeing what's going on if I have an actual form to look at. :-)
    Hilary Stoupa

  • 09-13-2018 06:23 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    Hey Hilary,

    Thanks for responding  :)

    I have attached the form in question.  Please note that in order to simplify my post before, I claimed "State" was the third tier of the cascading drop-down.  In actuality it is SalesOrg.  I just didn't want to go into explaining what that is, but it is a field specific to my company and the way we organize selling regions.

    In order to test the form, you will need to set the following values in the form:

    Rule 1:

    Type of Request: Request New BPID

    RA Sales Region: EMEA

    Country: AE - Utd.Arab Emir.

    Sales Organization: 4004 - UAE

    Use Default Sales Org Values: TRUE

     

    Rule 2:

    Type of Request: Request New BPID

    RA Sales Region: NA

    Country: US - USA

    Sales Organization: 1000 - US

    Use Default Sales Org Values: TRUE

     

    You will also notice that I attempted to implement your "Trigger" solution found elsewhere on this site.  It also didn't work.  :)

     

    Also, to be clear, I would DEFINITELY PREFER that the cascaded drop-downs system were only 2-tier, and the Sales Org values were populated automatically using rules (consequently populating the other 3 fields as well) off of the Country field.  The problem here is that, despite there being a many-to-one relationship between countries and Sales Orgs, there are currently 252 countries on this great planet of ours - even if there are only 75 Sales Orgs here at my company.  And that sounds like an awful lot of rules to create - even if I was able to get it to work.

    AND SO...I created an XML Table yesterday containing all of the columns that I need to auto-populate for each country.  I created it in Excel 2013, saved it as "XML Spreadsheet 2003 (*.xml), manipulated the header until it became readable by InfoPath, uploaded to SharePoint 2013 Online, and then created a new list for testing with single line of text columns.  Opened in InfoPath, created the secondary data connection, converted the controls, and linked Region and Country to the XML table.  I created a rule with no condition (field changes) to set the SalesOrg as follows:

    xdXDocument:GetDOM("XML-Values_XML_V5")/ss:Workbook/ss:Row/ss:SALESORG[../ss:COUNTRY = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Country]

    This was as per another of your solutions for someone (do you recall Role populating Model User?)  It also didn't work.  I am thinking that I need to establish relationships in the XML file, but I still need to research how that is done.

    Thanks for taking a look at this for me!

     

    -Jake

     

     

    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-13-2018 08:45 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    What is the field name for the country field in question? I see a number of labels on the form with the word "Country" and want to make sure I'm looking in the right place. If the data source for the sales org dropdown contains the country, we should be able to set that with a single rule, just using XPath to get the correct sales org for the selected country.
    Hilary Stoupa

  • 09-13-2018 10:08 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

     Country4.  Screenshot attached.

     

    EDIT: So, that might be relevant.  In this current state, the 3-tier cascaded drop-down is utilizing three secondary connections - one for RA_Sales_Region, one for Country (containing Region as a lookup for filtering), and one for Sales Org (containing Country as a lookup for filtering).


    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-13-2018 10:19 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    Cool. In the data connection Sales Orgs1, is SOCountryLook a lookup to your countries list?
    Hilary Stoupa

  • 09-13-2018 10:21 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

     That is exactly right.

    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-13-2018 10:38 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    What about these rules on the use default sales org values selection? I see the rules are using "contains" - but it looks like if I have the sales org, I can get the values from the CMD_Pulldown data source. Does the sales org in that data source match the name of the selected sales org? Or do we have to use contains here?
    Hilary Stoupa

  • 09-13-2018 10:59 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

     From my initial post:

    "Please note that I am unable to use "=" (instead of "contains") and "Type text..." here for the value, since this is a drop-down value and it is being seen as an integer I believe (ID?).  With an operator of "=", I only have these options: "Type a number...", "Select a field or group...", and "Use a formula...".  I am, however, able to instead refer to the original State fields in the data connection (as query fields or as data fields) and take advantage of the "=" operator along with a text string for the value.  The behavior of the 2 rules is exactly the same as above.  Again, uncertain if any of this is helpful, however."


    So is there any advantage to going in the direction of the XML Table if I want to auto-populate values in say, 5 different columns?  I am simply wondering if there is a way to avoid creating 75 different rules (SalesOrg7) or 252 rules (Country4)...


    P.S. Also please note the edit I added to the post containing the screenshot:

    "EDIT: So, that might be relevant.  In this current state, the 3-tier cascaded drop-down is utilizing three secondary connections - one for RA_Sales_Region, one for Country (containing Region as a lookup for filtering), and one for Sales Org (containing Country as a lookup for filtering)."


    -Jake

    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-13-2018 11:03 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    We can use the ID from the main data source sales org to look up the text in the Sales Org 1 data source for the comparison. So, no, you don't have to use XML or contains. That's why I'm asking if the text value in the list that is used for the CMD_Pulldown is equal to the text value displayed in the sales org dropdown.
    Hilary Stoupa

  • 09-13-2018 11:44 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    CMD_Pulldown is a SharePoint list that is being used for the values of a handful of columns (that have no relationship as list items).  Formerly, it was being used for Country and Sales Org, but I have since created separate lists in order to tackle the cascading drop-down function.  Yes the values are the same between these two columns in CMD_Pulldown, and the Sales_Orgs1 and Countries connections.

    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-13-2018 11:58 AM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    I was just wondering if there were values in there that correspond to the fields you want to set. I've taken some guesses. Here's a copy of your form, save it locally, right click and preview and we'll see if I've made any correct guesses. I can't test my changes in preview because I don't have access to your site, of course.
    Hilary Stoupa

  • 09-13-2018 12:22 PM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    It works.  :)

    Well, for SalesOrg anyway.  Again, there are no linear relationships in the CMD_Pulldown SP list.  It is only a repository of columns and column values stored vertically (think Quick Edit for adding new values to a column).  No list items here make any sense.

    That being said, I kind of understand the XPath argument here.  It looks like I should be able to take the Excel version of my XML Table (which does indeed contain column values in relational rows for each country) and load it into SP as a Custom List and do exactly the same thing that you have done with Currency.  Does that sound correct?

    P.S.  Nice job  :)

     

    -Jake

    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

  • 09-13-2018 12:50 PM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    Yup - as long as you have a way to relate the sales org. If your list stores the text value, you just have to have the additional filter to get that text from the secondary data source leveraging the ID that is currently stored in the main data source. The XPath looks hairy, but it is just matter of adding a filter and selecting the filter values correctly :)
    Hilary Stoupa

  • 09-13-2018 01:53 PM In reply to

    Re: Cascading Drop-down Value not Auto-populating Additional Fields

    You have been a great help, thank you so much.  :)

    I brought in the Default-Values SP list I created as a secondary connection, and am attempting to modify the Set Currency rule XPath you left as an example, but it is only bringing in the same value for Payment Terms (Payment1 in the form) for all Sales Orgs.  Is it clear to you what I am doing wrong?

    xdXDocument:GetDOM("Default-Values")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Payment_Terms[../d:Sales_Org = xdXDocument:GetDOM("Sales_Orgs1")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title[../d:ID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:SalesOrg7]]

     

    -Jake


    -Jake

    Factum est illud, fieri infectum non potest. (The Onion)

Page 1 of 3 (31 items) 1 2 3 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.