AnsweredAssumed Answered

Need help with relationship

Question asked by SteveKeiser on May 14, 2014
Latest reply on May 15, 2014 by philmodjunk

Like • Show 0 Likes0
Comment • 3

Title

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

philmodjunk May 15, 2014 10:14 AM

Correct Answer

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.

See the reply in context

No one else had this question

Outcomes

philmodjunk May 14, 2014 4:02 PM

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.

Actions

SteveKeiser May 14, 2014 5:59 PM

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

Actions

philmodjunk May 15, 2014 10:14 AM

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

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

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.

Actions

3 Replies

philmodjunk May 14, 2014 4:02 PM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
SteveKeiser May 14, 2014 5:59 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
philmodjunk May 15, 2014 10:14 AM
Unmark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions

Need help with relationship

Title

Post

Outcomes

Related Content

Recommended Content