I think I did a poor job of explaining myself...

I think I did a poor job of explaining myself after taking a look at an earlier post. I gave it another shot.

I need some assistance with generating an automatic response based on a couple of conditions in other tables. In my survey table [VPRAITbl], which is the parent table to the [OutcomeTbl] (connected via the VPRAIID) an automated response is generated based on the clientโ€™s โ€œScoreโ€ and their โ€œChargeCategoryโ€. I have a lookup table [VPRAIResultsTbl] which contains all of the possible responses. I have an App Formula in the [VPRAITbl] โ€œRecommendationโ€ field that complete this: =ANY(SELECT(VPRAIResultsTbl[Results],AND(IN([Possibility],[_THISROW].[ChargeCategory]),[Score]=[_THISROW].[Score]))),

and it works just fine.

However, now I need to generate an automatic response in the [OutcomeTbl] based on the clients โ€œScoreโ€ in the [VPRAITbl] and the โ€œChargeCategoryโ€ in the same table referencing the [VPRAIResultsTbl] where the response is located. When I write my formula (listed below), based on the above formula, I keep getting an error and canโ€™t seem to figure it out. Basically, when the โ€œScoreโ€ field in both the [VPRAITbl] & [VPRAIResultsTbl] match AND the โ€œChargeCategoryโ€ field in both the [VPRAITbl] & [VPRAIResultsTbl] match, I need the corresponding text from the field โ€œReleaseRecommendationโ€ in the [VPRAIResultsTbl] to appear in the field โ€œReleaseRecommendationโ€ in the [OutcomeTbl].

Here is my formula thus far which does not work:

=ANY(SELECT(VPRAITbl[ChargeCategory],AND(IN([Possibility],[_THISROW].[ReleaseRecommendation]),[Score]=[_THISROW].[Score])))

I am guessing that I am missing something with the VPRAIID? Thank you in advance for your assistance.

0 6 383
6 REPLIES 6

tony1
Participant V

@Thomas_Cunningham Whatโ€™s the error message?

If โ€œVPRAITblRefโ€ is the link column, what you want is probably:

=ANY(SELECT(VPRAIResultsTbl[ReleaseRecommendation]), AND([ChargeCategory]=[VPRAITblRef]. [ChargeCategory],[Score]=[_THISROW].[Score])))

Thanks for the assistance. I think this is close, but when entered, I get a โ€œSELECT has invalid inputsโ€,

which is something Iโ€™ve not seen before. Any ideas?

=ANY(SELECT(VPRAIResultsTbl[ReleaseRecommendation]), AND([ChargeCategory]=[VPRAITblRef].[ChargeCategory],[Score]=[_THISROW].[Score]))

I did attempt to make an edit as I was thinking that IN needed to be included because the ChargeCategory field in the VPRAITbl is a EnumList type.

=ANY(SELECT(VPRAIResultsTbl[ReleaseRecommendation]),AND(IN[ChargeCategory]=[ VPRAITblRef].[ChargeCategory],[Score]=[_THISROW].[Score])))

Iโ€™m stuck at this point. Thank you in advance for your assistance.

Thereโ€™s an unnecessary close parentheses, ) before the AND

Thank you for the tip. I did remove it, but i continue to be stuck. So far, this is what my expression looks like:

=ANY(SELECT(VPRAIResultsTbl[ReleaseRecommendation],AND(IN([Possibility]=[VPRAITbl].[ChargeCategory],[Score]=[_THISROW].[Score]))))

Iโ€™m not sure where Iโ€™m going wrong. Thank you for your assistance.

Hereโ€™s a screen shot of the error

Top Labels in this Space