Selecting the same value in two fields

[#1]

Hi everyone,

I have a list of about 100 values that appear in two fields. I want to be able to select one of those values and sum all Amounts if that value appears in either field. I'd also want to avoid any double-counting (if the value was present in both fields).

E.g., the data would look like:

Field1	Field2	Amounts
A	B	20
B	C	2
C	A	10
A	A	5
D	F	10

And if I selected "A", the red amounts would be selected. What kind of expression would I have to write to make this possible?

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#3]

This is awesome! Thank you for introducing me to P()

However, one more question - how can I create a list box for those 100 values for users to select, rather than have them search in list boxes for Field1 or Field2?

I saw a solution using "=Field1 & '-' & Field2", but that creates a combination. I would rather the list box was just

A

B

C

and their selection used your expression across Field1 and Field2.

[#4]

And another one:

- sum ONLY when selected values are BOTH in Field1 and Field2

[#5]

I noticed one problem with your solution - I actually have other fields, and making selections in them seems to affect this expression. How do I prevent that from happening?

[Gysbert_Wassenaar]

Create an alternate state on the General tab of the Document Properties window. Then assign the new alternate state to the straight table on the General tab of its properties window.

[#7]

Of course... thank you