Solved: IF(IN(LOOKUP(maxrow( PROBLEM - Page 2

apocunningham · 09-13-2019 06:40 AM

I could use some assistance with identifying my error in my expression within my app. My expression (see below) is not causing an error rather, it is not working correctly. I am entering the expression into Question11 of the VPRAITbl, ValidIf option.

IF(IN(LOOKUP(maxrow(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID]), CrimeHistoryTbl, ID, OffenseInfo), LIST(“Juvenile”)), LIST(True), LIST(false))

I think the problem is that I am incorrectly using the ‘MAXROW’. When I have tried to use a similar expression only using the IF(IN(LOOKUP portion, I begin to get errors. Basically, if the client has past conviction in the CrimeHistoryTbl in the Offenseinfo field (EnumList) that are indicated to be “Juvenile” offenses, then Question11 (Enum) of the VPRAITbl has to be true, else false. I have entered a test client with generic information and given him a “Juvenile” offense, but the results of the expression is false. I think my use of MAXROW is only looking at the last entry or only one of several entries. A person can have multiple previous convictions and if any or many of them are indicated to be “Juvenile” then Question11 must be true.

Your assistance is much appreciated!

apocunningham

Wow! I now realize that I made a number of mistakes. I am beginning to see that I’m in way over my head. Thank you for all your help with this. Let me try to explain how I ended up here. The App that I am currently working on is version 2 of the one we have been using for last 18 or so months. After using the previous version for some time, we learned that we were capturing a bunch of useless data and that the structure of the App was poor and not condusive to generating a Word document report for court without a huge amount of leg work. I created a new version (the one I’m currently working on) and have made numerous changes. One of the things that I failed to do was to remove the =uniqueID from each of the child tables to the Courttbl. Some had been removed and when I created the diagram above, I did not check each before creating it and assumed that =uniquedID was there for each. Luckily, there is only one record in each of these child tables and despite the fact that the =uniqueID was present, the App did grab the correct ID from the CourtTbl, i.e. CourtID.

I have since taken the table structure advice of Dave (see below) and have updated all child tables to the CourtTbl and the references. I have also updated all of the expressions, mostly in the VPRAITbl to reflect the correct column name, i.e. CourtID

As for Question11, I started with Steve’s suggested expression and made some changes to reflect the changes to the table structure (see below).

ISNOTBLANK(FILTER(“CrimeHistoryTbl”,AND(([CourtID] = [_THISROW].[CourtID]),IN(“Juvenile”,[OffenseInfo]))))

Long story short, the expression for Question11 is now working correctly, which is GREAT, THANK YOU! The down side is that I am not done.

Again, THANK YOU for all your help, I appreciate all the user of the app sheet community coming to my rescue.

View solution in original post