Determine if a list of available values in field1 is in field2.

[#1]

Hello,

I am trying to create a calculation that sums all the corresponding records from one table based on the matching values between the summed table and another table.

For example, in the attached test file I have a SalesTeam and ProductTeam table. SalesTeam and ProductTeam have overlapping team name values but the columns are intentionally named differently (this is required for my data model for security reasons). If the available SalesTeam team name values exist in the available ProductTeam team name values, I want to show a sum of the sales amount.

i.e. If no Product Teams are selected, the total should show 500 + 1000 + 400 = 1900

     If Team1 is selected, the total should show 500

     If Team1 and Team2 is selected, the total should show 500 + 1000 = 1500

I am attempting to do this with an If statement like:

     If(Match(SalesTeam, Concat(ProductTeam, ',')), Sum(Amount))

as well as:

     If(SalesTeam=ProductTeam, Sum(Amount))

However, you'll notice that these only work when there are single values selected for ProductTeam and SalesTeam.

How can I make these expressions work if there are one or more values selected in ProductTeam and none selected for SalesTeam?

[swuehl · Accepted solution]

Try as expression in a text box

=Sum({<SalesTeam = p(ProductTeam)>} Amount)

[#3]

That's exactly what I needed. Thank you!