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.
@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
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |