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

[Thomas_Cunningh]

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.

[tony1]

@Thomas_Cunningham What’s the error message?

[Bellave_Jayaram]

If “VPRAITblRef” is the link column, what you want is probably:

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

[Thomas_Cunningh]

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.

[Bellave_Jayaram]

There’s an unnecessary close parentheses, ) before the AND

[Thomas_Cunningh]

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.

[Thomas_Cunningh]

Here’s a screen shot of the error