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".
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.)
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.