3 Replies Latest reply on May 15, 2014 10:14 AM by philmodjunk

    Need help with relationship

    SteveKeiser

      Title

      Need help with relationship

      Post

           I have the following scenario which I can't get to work right:

           Table: "Sales Item" Table Occurence: salesitem_SALES_repmatch

           Relationship:  salesitem_SALES_repmatch --< Sales where table occurence salesitem_SALES_repmatch::cRepSelected = Sales::cRepFilter

           Field:   Sales Item::cRepSelected = if ( Rep = RepSelected ; 1 ; "" )

           Field:  Sales::cRepFilter = if ( Sales Item::RepSelected = "None" ; 0 ; if ( Sales Item::RepSelected not equal to "None" ; 1 ; "" ))

            

           This is not working. I am not smart enough to figure this out. My objective is to allow the user to select a filter which then determines a filtered value list, or unfiltered in the event they select "None" as the filter. The value list comes from a field in the table "Sales Item".

        • 1. Re: Need help with relationship
          philmodjunk

               salesitem_SALES_repmatch::cRepSelected = Sales::cRepFilter

               

                    The value list comes from a field in the table "Sales Item".

               And on what layout will you use that value lists? On what table occurrence is that layout based?

               Exactly how do you need this filtered list to work? What kind of values get entered into Rep and RepSelected? How do they get those values?

               Note:

               cRepSelected could be simplified to be: Rep = RepSelected

               and cRepFilter could be simplified to: Sales Item::RepSelected ≠ "None"

               But these changes should not affect how or whether your relationship works, they're just simpler expressions to get the same values.
                

          • 2. Re: Need help with relationship
            SteveKeiser

                 The value list will be used on a layout on the "Sales" table. There is only one table occurernce for the "Sales" table.

                 The value list will be used so that each sales rep can select to see only those items previously sold by that sales rep, or by all sales reps, or any other individual sales rep. When the sales rep clicks on a button, another window pops up where they select radio buttons which enters their selection into a global field salesitem_SALES_repmatch::RepSelected

            • 3. Re: Need help with relationship
              philmodjunk

                   The value list will be used so that each sales rep can select to see only those items previously sold by that sales rep, or by all sales reps, or any other individual sales rep.

                   I read that as needing to see all items sold by a specific sales rep or all items by all sales rep. ("That sales rep" is just another example of specifying a specific sales rep.)

                   

                        When the sales rep clicks on a button, another window pops up where they select radio buttons which enters their selection into a global field salesitem_SALES_repmatch::RepSelected

                   I will assume that the radio button values are Sales Rep names (hope you don't have reps with the same name or that want to change their names...) The fact that RepSelected is a global field is part of why this fails. This makes cRepSelected an unstored, unindexed field and keeps the relationship from working

                   Here's an alternative approach. I'm going to use simpler names for your two tables occurrences as they are very cumbersome to type. Substitute your names for mine:

                   Sales------<SalesItems|ByRep

                   Sales::SelectedRep = SalesItems|ByRep::cRep

                   Define cRep as a calculation field with text as the result type:

                   List ( Rep ; "-All-" )

                   Now, selectedRep can be set up with a value list of Sales Reps and -All- as the values. Select -All- and you get a value list of all values. Select a specific Rep and you see a list of only the SalesItems sold by that Rep.

                   This trick works because return separated lists of values (such as the name or ID of a Sales Rep and "-All-") match to related records by any one value in the list.