Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team
Common Tasks
recent posts
Tags
General
Office Blogs
Archives
Archives

August, 2006

MSDN Blogs > Microsoft InfoPath 2010 > August, 2006
Sort by: Most Recent | Most Views | Most Comments
Excerpt View | Full Post View
  • Microsoft InfoPath 2010

    One-to-Many Relationships on Database Connections

    Posted over 7 years ago
    by
    • 9 Comments
    There have been a few questions about how the main database submit data connection works with related tables.  This is an FYI to clear up some of those issues, as I don't think this information ever made it public!
     
    At least one of the relationships for every pair of related tables must include the left-hand table's primary key (PK) (where A is the left-hand table in "A relates to B on A.ID, B.ID").  Without this stipulation, there may be many records in table A that map to one record in table B along the defined relationship.
     
    Note also that unique indexes and unique constraints allow nulls, so the primary key must be used in at least one relationship (they don't allow nulls).  Basically, InfoPath enumerates the records to be updated according to the primary key.  This way, the data inserted into the database is well-defined.
     
    What that boils down to is this:
    • Sufficient relationships:
      • PK --> Non-unique,  Unique, or PK
    • Insufficient relationships (assuming a sufficient relationship hasn't already been defined):
      • Non-Unique --> Non-unique,  Unique, or PK
      • Unique --> Non-unique,  Unique, or PK
     
     
    Once you've defined one sufficient relationship (as listed above), then you can define any other additional relationships that you want (they don't have to follow my rules listed above).
     
    - Forrest
    Software Development Engineer in Test
  • Microsoft InfoPath 2010

    Enforcing unique values in a repeating list

    Posted over 7 years ago
    by
    • 1 Comments
    Have you ever created a form which allows the user to choose items from a list and you wanted to make sure the user doesn't choose the same item twice?  If you've got InfoPath 2007 you can use the new Multi Select List Box, but if you've got InfoPath 2003, you're still in luck, this blog entry is for you!
     
    Note: You should be familiar with XPath expressions before preceding.
     
    Let's start with a repeating table with a dropdown control bound to a secondary data source.
     
     
    There are a few choices when it comes to enforcing unique values selected by the user:
    1. Only show values that have not already been selected by the user.
    2. Show a validation error when the user selects something which has already been selected.
    3. Write code using the Changing event.
     
    This blog entry will cover both options 1 and 2.
     
    Option 1 - Only show values that have not already been selected by the user
     
    Only showing certain values implies that a filter is being applied to the dropdown.  To apply a filter, click the Filter Data… button when selecting the entries for the dropdown to show the condition builder.  Unfortunately, there is no UI in the condition builder to build an expression that means "don't show anything that is already in the list".  Thus, we'll have to construct this manually by selecting The Expression.
     
     
    You might be tempted to put the following expression:
    . != xdXDocument:get-DOM()/my:myFields/my:items/my:item/my:product
    This will return true if any one value from the list matches the current value.  In other words, this condition will always return true if there are two entries in the list which are different.  (Definitely not what we are looking for)
     
    The correct condition is a slight adjustment to the former expression:
    not(. = xdXDocument:get-DOM()/my:myFields/my:items/my:item/my:product)
    This will only return true if none of the values from the list matches the current value.  In other words, only values that are not in the list will be displayed.
     
    Preview the form and you will see that the dropdown only lists the products that have not already been selected.
     
     
     
    Option 2 - Show a validation error when the user selects something which has already been selected
     
    The only way this will work properly is if we add a hidden calculated field along with the product selection.
    (The reason for this is a bit too complex to explain here, so it'll have to wait for another time.)
     
     
    The formula for the unique field is the following:
    not(../my:product = (../preceding-sibling::my:item | ../following-sibling::my:item)/my:product)
    This will set Unique to true if and only if the product isn't already selected preceding or following the current item.
     
    The next and final step is to use data validation on the dropdown to show an error.
     
     
    Preview the form and you will see that the dropdown will show a validation error if the items are not unique.
     
     
    - Gary
    Software Development Engineer
Page 1 of 1 (2 items)