IF(IN(LOOKUP(maxrow( PROBLEM

[apocunningham]

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!

[Steve]

apocunningham:

I think my use of MAXROW is only looking at the last entry or only one of several entries.

That’s exactly what MAXROW() does: it finds the one row of a table that has the highest value in a given column. Your use:

maxrow(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID])

says, “give me the row in the VPRAITbl table having the highest value in the VPRAIDateEntered column, considering only those rows where the ID column value matches the ID column value of this form.” Or, put more simply, “give me the VPRAITbl entry with the newest VPRAIDateEntered for this ID.”

What is the goal you are using MAXROW() to try to achieve?

apocunningham:

A person can have multiple previous convictions and if any or many of them are indicated to be “Juvenile” then Question11 must be true.

I’m curious why you’re asking the app user to supply an answer that the app can answer itself? And limiting the user to only providing the answer the app itself has already chosen? Why ask the user at all?

[apocunningham]

Thank you for your assistance! I do believe that I should not be using MAXROW and am struggling with how to re-write the expression using IF(IN(LOOKUP. The goal in the VPRAITbl, Question11’s expression would be to scan all of the records in the CrimeHistoryTbl, OffenseInfo field that belong to this client and if one of those records in the OffenseInfo field (EnumList) indicates “Juvenile” then make Question11 “True”.

Steve:

I’m curious why you’re asking the app user to supply an answer that the app can answer itself? And limiting the user to only providing the answer the app itself has already chosen? Why ask the user at all?

I agree with your question, If “Juvenile” is a part of one of the CrimeHistoryTbl records, then it is true and it would be great if this was just automatically selected and the user did not have to do so. If it is not True, then it is False and be automatically selected accordingly. I am assuming that using ValidIf is also incorrect in this example?

If I re-write this to:

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

I get an error (see below) and cannot tell where I am placing an incorrect parentheses.

Expression ‘IF(IN(LOOKUP(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID]), CrimeHistoryTbl, ID, OffenseInfo)), LIST(“Juvenile”)), LIST(True), LIST(false))’ could not be parsed due to exception: Number of opened and closed parentheses does not match.

What are your thought or even if this is somewhere near correct? Thank you again!

[Bahbus]

apocunningham:

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

Well the expression is all kinds of broken. You have too many closing parenthesis.
LOOKUP(find-value, in-dataset, in-column, return-column)
You only have three arguments.

IN(match-value, in-list)
You have have four arguments.

I also feel like you should be doing this as a regular app formula for the column rather than a Valid_If.

[Steve]

Ah! More insight! So this:

MAXROW(
  "VPRAITbl",
  "VPRAIDateEntered",
  ([ID] = [_THISROW].[ID])
)

I’m guessing is an attempt to get the ID column value from the VPRAITbl entry the user is in the process of entering? If so, it would seem to be entirely unneeded and could be replaced entirely with:

[_THISROW].[ID]

Yes, just [_THISROW].[ID], since that already refers to the value of the ID column of the row currently in the form, which will be a row in the VPRAITbl table. If that guess is correct, we can simplify your original expression:

IF(
  IN(
    LOOKUP(
      [_THISROW].[ID],
      "CrimeHistoryTbl",
      "ID",
      "OffenseInfo"
    ),
    LIST(“Juvenile”)
  ),
  LIST(TRUE),
  LIST(FALSE)
)

LOOKUP() finds only the first matching row, but you said there may be multiple rows in CrimeHistoryTbl for any given ID value, so LOOKUP() may not provide all needed information (as you’ve noticed). So you need a better way. Here’s my suggestion, again assuming that the ID column value in VPRAITbl and CrimeHistoryTbl have the same meaning:

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

1. FILTER("CrimeHistoryTbl", AND(..., ...)) finds all rows in CrimeHistoryTbl that match both of the given criteria (AND(..., ...); see (2) & (3)).

2. ([ID] = [_THISROW].[ID]) matches only those rows in CrimeHistoryTbl with an ID column value that matches the ID column value of the form. This answers the question, “does this CrimeHistoryTbl row correspond to the person identified in the VPRAITbl row in the form?”

3. IN(“Juvenile”, [OffenseInfo]) matches only if the EnumList in the OffenseInfo column of the row in the CrimeHistoryTbl table contains a Juvenile entry. This answers the question, “does this CrimeHistoryTbl row describe someone with a Juvenile offense?”

4. ISNOTBLANK(...) answers the question, “were any matching rows found?”

This expression could be used as the initial value expression for Question11, eliminating the need for a Valid If expression altogether.

[apocunningham]

Hello, and again thank you for all the help. I tried the expression below and while it does not get any error messages, it does not work correctly.

Steve:

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

When I look at the explanation of the expression in the app (see below), I think I see the problem, but am unsure how to fix it. I have Bold the piece that i think is causing the problem.

ISNOTBLANK(
…The list of values of column 'CrimeID’
…from rows of table ‘CrimeHistoryTbl’
…where this condition is true: (ALL these statements are true:
…1: (The value of column ‘ID’) is equal to (The value of ‘ID’ from the row referenced by ‘ID’)
…2: (“Juvenile”) is one of the values in the list (The value of column ‘OffenseInfo’)))

“…The list values of column” should be “ID” not CrimeID. There is a CrimeID field in the CrimeHistoryTbl, but it is only there to allow for a one to many relationship to the ClientTbl which is the Parent Table.

The ClientTbl is the parent to the CrimeHistorytbl (one to many relationship) and they REF each other via the field “ID”. The CrimeHistoryTbl as a second field labeled CrimeID using =uniqueID just to allow for multiple entries under the same ID.

I hope I’m making sense. Thank you again.

[Steve]

apocunningham:

“…The list values of column” should be “ID” not CrimeID.

That actually shouldn’t matter, since the expression isn’t concerned with the meaning of the values, but merely that values are returned or not.

I’m more inclined to suspect I misunderstood the meanings of the ID columns in the CrimeHistoryTbl and VPRAITbl tables. Can you explain the meanings of those two columns?

[apocunningham]

Thank you for taking so much time with this!

The “ID” Field/column in both the VPRAITbl and CrimeHistoryTbl is what I would call the “Primary Key”. the Unique identifier for each record. I created a diagram of the APP’s table structure if it helps.

When I try to use the IF(IN(LookUP expression you wrote, I get the following error message:

Column Name ‘Question11’ in Schema ‘VPRAITbl_Schema’ of Column Type ‘Yes/No’ has an invalid ‘Initial Value’ of ‘=IF( IN( LOOKUP( [_THISROW].[ID], “CrimeHistoryTbl”, “ID”, “OffenseInfo” ), LIST(“Juvenile”) ), LIST(TRUE), LIST(FALSE) )’. The type of the Initial Value does not match the column type. Consider using ‘’ instead.

To be honest, I’m a bit twisted around on this and don’t know where to go from here. What are you’re thoughts? Thanks for everything!

[Steve]

You’re diagram doesn’t make sense to me. If all of the ID columns have their own UNIQUEID() values, there are no relationships.

As for the initial value expression, try using the second expression I gave above:

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

[Bahbus]

I think I understand your diagram…

So first, I’m going to go through the way I think your data should be setup.

ResidenceTbl, EmploymentTbl, SocialInfoTbl, SupervisionTbl, VPRAITbl, and DALETbl all have a column named CourtID (that will help keep the associated IDs together). This column should be a Ref column and they should all point to the CourtTbl and have matching values it’s CourtID.

CourtTbl and CrimeHistoryTbl both have a column named ID and is a Ref column pointing to ClientTbl. ClientTbl’s ID column should be a regular type of field (text, number, etc.).

Assuming we are still trying to solve the original issue of…

apocunningham:

Question11 (Enum) of the VPRAITbl has to be true, else false

…then you should set the column formula to be…

Steve:

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

If the data is setup correctly, the above should work no problemo. However, I also parrot Steve’s thought about the “=uniqueID” you have shown for each one in the diagram. Each field isn’t generating it’s own uniqueID separately…right?

[apocunningham · Accepted solution]

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

Bahbus:

ResidenceTbl , EmploymentTbl , SocialInfoTbl , SupervisionTbl , VPRAITbl , and DALETbl all have a column named CourtID (that will help keep the associated IDs together). This column should be a Ref column and they should all point to the CourtTbl and have matching values it’s 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.