Cascading Dropdowns Missing data after Filter - InfoPath Dev
in

InfoPath Dev

Cascading Dropdowns Missing data after Filter

Last post 06-04-2009 05:45 PM by Shanty. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next

  • 05-27-2009 07:21 PM

    • Shanty
    • Not Ranked
    • Joined on 01-06-2008
    • Posts 8

    Cascading Dropdowns Missing data after Filter

    Reply Contact

    I have read through many forums covering similar issues but none have quite equated to the issue I'm having.  I am fairly new to working with Infopath xPath and filters and am having difficulty with cascading dropdowns that are fed by a SQL query.  Here's the scenario...

     I am working with Infopath 2003 and have 4 dropdowns, 3 of which are populated by a single SQL query.  DropDownA (DDA) has a many-to-many relationship in SQL with DDB and DDB has a many-to-many relationship with DDC.  Although there are interim tables in the database to handle this, all the data is brought to the Infopath form as a single query to avoid too many data connections.  Now, I have DDB showing unique values based on the selection in DDA using the filter not(.=../preceding-sibling::*/@DDB-SQLFieldName) and DDA-SQLFieldName=DDA-InfopathFieldName however some of the values are missing in DDB because they are already appearing for an earlier item in the list of DDA. 

     With my limited understanding of xPath and the fact that this information is a copy from a forum posting here, I believe what I have as the filter is very close to what I need and I am hoping I can get a nudge in the right direction. Thanks.

     -Shanty

  • 05-28-2009 09:08 AM In reply to

    Re: Cascading Dropdowns Missing data after Filter

    Mark as Answer
    Reply Contact

    Can you provide a sample of your data source sturcture and the filters you have on each dropdown?

    Please Visit:
    Braintrove
    Greg's Paintings

  • 05-29-2009 12:30 PM In reply to

    • Shanty
    • Not Ranked
    • Joined on 01-06-2008
    • Posts 8

    Re: Cascading Dropdowns Missing data after Filter

    Mark as Answer
    Reply Contact
    Thank you for responding so quickly. I hope I am about to explain this effectively.The filter on DropDownA:  not(. = ../preceding-sibling::*/@Site)

     

    DropDownB <xsl:choose> <xsl:when test="function-available('xdXDocument:GetDOM')"><option/> <xsl:variable name="val" select="my:ClientInfo/my:Dept"/><xsl:if test="not(xdXDocument:GetDOM(&quot;AreaSiteDeptSubdept&quot;)/dfs:myFields/dfs:dataFields/d:AreaSiteDeptSubdept/@DeptName[(not(. = /preceding-sibling::*/@DeptName)) and (../@Site = xdXDocument:get-DOM()/my:Account/my:ClientInfo/my:Site)][.=$val] or $val='')"> <option selected="selected"><xsl:attribute name="value"> <xsl:value-of select="$val"/></xsl:attribute> <xsl:value-of select="$val"/></option> </xsl:if><xsl:for-each select="xdXDocument:GetDOM(&quot;AreaSiteDeptSubDept&quot;)/dfs:myFields/dfs:dataFields/d:AreaSiteDeptSubDept/@DeptName[(not(. = /preceding-sibling::*/@DeptName)) and (../@Site = xdXDocument:get-DOM()/my:Account/my:ClientInfo/my:Site)]"> <option><xsl:attribute name="value"> <xsl:value-of select="."/></xsl:attribute> <xsl:if test="$val=."><xsl:attribute name="selected">selected</xsl:attribute> </xsl:if><xsl:value-of select="."/> </option></xsl:for-each> </xsl:when><xsl:otherwise> <option><xsl:value-of select="my:ClientInfo/my:Dept"/> </option></xsl:otherwise> </xsl:choose>================================= DropDownC <xsl:choose> <xsl:when test="function-available('xdXDocument:GetDOM')"><option/> <xsl:variable name="val" select="my:ClientInfo/my:SubDept"/><xsl:if test="not(xdXDocument:GetDOM(&quot;AreaSiteDeptSubDept&quot;)/dfs:myFields/dfs:dataFields/d:AreaSiteDeptSubDept/@SubDept[../@DeptName = xdXDocument:get-DOM()/my:Account/my:ClientInfo/my:Dept][.=$val] or $val='')"> <option selected="selected"><xsl:attribute name="value"> <xsl:value-of select="$val"/></xsl:attribute> <xsl:value-of select="$val"/></option> </xsl:if><xsl:for-each select="xdXDocument:GetDOM(&quot;AreaSiteDeptSubDept&quot;)/dfs:myFields/dfs:dataFields/d:AreaSiteDeptSubDept/@SubDept[../@DeptName = xdXDocument:get-DOM()/my:Account/my:ClientInfo/my:Dept]"> <option><xsl:attribute name="value"> <xsl:value-of select="."/></xsl:attribute> <xsl:if test="$val=."><xsl:attribute name="selected">selected</xsl:attribute> </xsl:if><xsl:value-of select="."/> </option></xsl:for-each> </xsl:when><xsl:otherwise> <option><xsl:value-of select="my:ClientInfo/my:SubDept"/> </option></xsl:otherwise> </xsl:choose> The Data Source is a SQL database

    This may seem to be quite obscure, but so is the company's corporate structure.  The DeptSubdept table handles the many-to-many relationships that the other 3 tables (Site, Dept, Subdept) have with each other and ensures there are only one-to-many relationships.

     
    • FK_DeptSubdept_Dept
    • FK_DeptSubdept_Subdept
    • FK_DeptSubdept_Site 

    The query developed from these tables includes not only the unique IDs from the DeptSubdept table that links them all but also the text values for each of those and returns a table with all combinations.

     

     

     

  • 06-04-2009 05:45 PM In reply to

    • Shanty
    • Not Ranked
    • Joined on 01-06-2008
    • Posts 8

    Re: Cascading Dropdowns Missing data after Filter

    Mark as Answer
    Reply Contact

    Today I reposted my issue to the "Controls' forum and I believe it is better explained so if anyone following this forum can help out, I would greatly appreciate it because my continued research has not revealed the answer.  It is located at http://www.infopathdev.com/forums/t/12158.aspx.   

    Thanks all,

    -Shanty
Page 1 of 1 (4 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.