=IFS References

apocunningham · 08-26-2019 12:18 PM

I am hoping someone can see the mistake I am making in my expression (Below).

IFS(IN([ID].[EmploymentStatus], LIST(“PartTime”,“Unemployed”)), “Yes”)

I have a Yes/No Field in the VPRAITbl, “Question7”. I have a Enum field in EmploymentTbl, “EmploymentStatus” with the following options:

FullTime
PartTime
Unemployed
Retired
HomeMaker
Disability

Basically, if the user selects either PartTime or Unemployed in the EmploymentTbl, “EmploymentStatus” field. The answer to Question7 in the VPRAITbl has to be “Yes”. If not, I still want the user to be able to choose any field in Question7. I was attempting to use the expression in “initial value”. I had thought I was getting the hang of these references, but am not.

Any help is much appreciated. THANK YOU

MultiTech

Looks like you’re close. Put the formula in the valid if space, but instead of using IFS(), use IF() - this way you can include what should be allowed when the condition is false.

Also, instead of using “Yes” for the allowed value, you stated the column in question is actually a Yes/No - the values you use for this are true/false; so the formula would like something like this:

IF(IN([ID].[EmploymentStatus], LIST(“PartTime”,“Unemployed”)), 
  LIST(true), 
  LIST(true, false)
)

MultiTech

I do have a question about this fellow

Is this a reference column or the key of the table this formula is on?

apocunningham

The VPRAITbl and the EmploymentTbl are connected via the ID field in both tables, one to one relationship. Both have an ID field.

I did attempt the expression that you provided and I’m getting the following error:

Error in expression ‘[ID].[EmploymentStatus]’ : Unable to find column 'EmploymentStatus’

EmploymentStatus, Enum Type is the field name in the EmplymentTbl. Not sure why I am getting this error? Thank you for your assistance!

MultiTech

This stems from what I was asking about.

[Something].[Something] is a particular syntax for things in appsheet, and if used in the wrong circumstance it can cause confusion on the backend.

Try removing the [ID]. part and just leave the column by itself.

The syntax you are using is called a de-reference, you can read more here:

apocunningham

While spending more time thinking, I think I see your point. To try to explain, I think I need to outline the table structures because the ID field of both the VPRAITbl and EmploymentTbl is generated by the CourtTbl. Here goes:

ClientTbl is the Parent table of both the CrimeHistoryTbl and the CourtTbl, both are a one to many relationship connected via the ID field.

The CourtTbl is the Parent table to the EmploymentTbl, VPRATbl, ResidenceTbl, SocialInfoTbl, and SupervisionTbl. All are a one to one relationship and are connected via the CourtID field generated within the CourtTbl. In all the Child tables, EmploymentTbl, VPRATbl, ResidenceTbl, SocialInfoTbl, and SupervisionTbl the label for the ID field is “ID”.

Basically, a person can have many previous convictions, thus the one to many relationship between the ClientTbl and the CrimeHistoryTbl. He/She will only have one current charge (CourtTbl) and will only have one address, phone number and other factors at the time we are assessing for the current charge, i.e. EmploymentTbl, VPRAITbl, ResidenceTbl, SocialInfoTbl, and SupervisionTbl.

So, in this instance, Question7 of the VPRAITbl is only connected to the EmploymentStatus field of the EmploymentTbl through the ID field CourtTbl. CourtTbl branches off in several directions to all the child tables.

Using your suggestion to remove the [ID]., I get the following error: Unable to find column 'EmploymentStatus’

What are your thoughts and thank you again for your help!

MultiTech

Okay, inside the settings for the [ID] column, it should be set as a Ref column type yes? Is the Referenced table selected the correct one?

When I see things like this:

It’s saying that when it looks in the table indicated as the reference table in the [ID] column, it can’t find EmploymentStatus - this usually happens when you’ve set a column type as reference, but haven’t gone back and set the table it’s referencing to.

apocunningham

Thanks again for all the time you are putting into this, it’s much appreciated. I have included a snip of all the tables below, hopefully this helps.

ClientTbl: Parent table to the CrimeHistoryTbl & CourtTbl

CrimeHistoryTbl: Child table to ClientTbl, one to many relationship

CourtTbl: Child table to the ClientTbl, one to many relationship, Parent table to the EmploymentTbl, VPRATbl, ResidenceTbl, SocialInfoTbl, and SupervisionTbl

EmploymTbl: Child table of the CourtTbl, one to one relationship.

VPRAITbl: Child table of the CourtTbl, one to one relationship.

ResidenceTbl: Child table of the CourtTbl, one to one relationship.

SocialInfoTbl: Child table of the CourtTbl, one to one relationship.

SupervisionTbl: Child table of the CourtTbl, one to one relationship.

I am able to write de-reference expressions between the VPRAITbl and the CourtTbl, for example, I wrote this one last night: IF(IN([ChargeCategory], LIST(“ViolentFelonyFirearm”, “ViolentMisdemeanor”)), LIST(True), LIST(False)) and it seems to work fine, although it doesn’t actually click “True”, rather it removes “False” as an option when the condition is met. I am guessing that this is working because of the direct relationship between the VPRAITbl and the CourtTbl, that it doesn’t have to match to the CourtTbl and then match to the EmploymentTbl to then execute in the VPRAITbl within the problem that we are currently trying to resolve.

Thank you for taking the time to look at this!

MultiTech

When you put a formula inside the valid if space, it’s telling the app which values are allowed; so in your formula:

IF(
IN([ChargeCategory], LIST(“ViolentFelonyFirearm”, “ViolentMisdemeanor”)), 
  LIST(True),  <---You're making the list of acceptable values when the ChargeCategory IS one of the two listed
  LIST(False)  <----Same here, you're making a list of acceptable values
)

To automatically have things selected, put the formula inside the initial value space; this way the formula will evaluate things automatically, but people can still take over if they want (or can). You’ll have to alter the formula a bit (keep the valid if there, btw):

IF(IN([ChargeCategory], LIST(“ViolentFelonyFirearm”, “ViolentMisdemeanor”)), true, false)

In this version of the formula, we’re setting the value, NOT making a list.

And just FYI:

This is not a de-reference formula, those would look like this:

[ID].[Some_Column]

This will reach into the record selected in the [ID] column and pull the value from the [Some_Column] column.

apocunningham

This is great information, thank you for clearing up my mistake with actually selecting a value based on conditions as well as the de-reference issue. I will move the expression to the initial value field.

Did you have any thoughts on the “reference” issue between the EmploymentTbl and the VPRAITbl?

Thanks again for your help.

MultiTech

What’s the problem

apocunningham

I apologize for being unclear. the expression:
IF(IN([ID].[EmploymentStatus], LIST(“PartTime”,“Unemployed”)), **
** LIST(true), **
** LIST(true, false)
)
is not working and I continue to get the error: Unable to find column 'EmploymentStatus’

The only thing that I can think of that may be causing this is the table structures. As I mentioned, the ClientTbl is the Parent to both the CrimeHistoryTbl & CourtTbl with a one to many relationship connected via the ID filed. The CourtTbl is the Parent to the EmploymentTbl, VPRATbl, ResidenceTbl, SocialInfoTbl, and SupervisionTbl. In order for the relationship between the ClientTbl and the CourtTbl to be a one to many, there is a second ID field called CourtID. The CourtID within the CourtTbl is how the child tables: EmploymentTbl, VPRATbl, ResidenceTbl, SocialInfoTbl, and SupervisionTbl are connected to it. So in the expression:
IF(IN([ID].[EmploymentStatus], LIST(“PartTime”,“Unemployed”)),
LIST(true),
LIST(true, false)
)
The [ID].[EmploymentStatus] isn’t actually matching up with the ID within the EmploymentTbl, it seems to be matching with the ID field in the CourtTbl and there is no field in that table labeled, “EmploymentStatus”

I did a poor job of explaining myself previously when I was saying that I do have expressions between the VPRAITbl and the CourtTbl that are working. For example:
IFS(
IN(
[ID].[ChargeType],
LIST(
“Class I”,
“Class II”,
“Class II”,
“Class IV”,
“Class X”
)
),
LIST(
“DrivingUndertheInfulence”,
“NonViolentFelony”,
“ViolentFelonyFirearm”
),
IN(
[ID].[ChargeType],
LIST(
“Class A”,
“Class B”,
“Class C”
)
),
LIST(
“DrivingUndertheInfulence”,
“NonViolentMisdemeanor”,
“ViolentMisdemeanor”
)
)
This expression works fine and am guessing that it does because it is referencing the Parent table, i.e. CourtTbl. The question is, how do I reference between two child table, i.e. EmploymentTbl & VPRAITbl?

Again, you have been a huge help, I’m sorry that I am so new to this. THANK YOU!

MultiTech

This is where you can make use of a LOOKUP() formula instead of using a de-reference formula.

Both child tables share the parent link, so you can lookup the parent value in any of the child tables to find records and/or values.

If there is only one child record, and you can be confident there won’t be multiples, then you can use a lookup() by itself to find the data you want:

LOOKUP([_thisrow].[ID], Child_Table, Table_Key, Column_You_Want_data_From)

If you’re going to have more than one record in the child table, then you need to determine a way to find the one you want:

maxrow() - can give you the most recent
minrow() - the first
SELECT() - you can build your own criteria

To make this adjustment, it’s really just a modification to the LOOKUP() formula from above:

LOOKUP(
MAXROW(Child_Table, DateTime_Column, [Parent_Link] = [_thisrow].[ID]), 
Child_Table, Table_Key, Column_You_Want_data_From)

You can see here all we did was change the first part of the LOOKUP for another formula MAXROW().

Finally you add the formula from above into your original formula:

IF(
  IN(
    LOOKUP(MAXROW(Child_Table, DateTime_Column, [Parent_Link] = [_thisrow].[ID]), Child_Table, Table_Key, Column_You_Want_data_From), 
    LIST(“PartTime”,“Unemployed”)
  ), 
  LIST(true), 
  LIST(true, false)
)

apocunningham

I think I’m getting close. I wrote the expression as follows and placed it in the initial value.

IF(IN(LOOKUP(MAXROW(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID]), EmploymentTbl, ID, EmploymentStatus), LIST(“PartTime”,“Unemployed”)), LIST(True), LIST(True, false))

I did not initially get a error message until I save the expression and the app updated. At that point I got:

Column Name ‘Question7’ in Schema ‘VPRAITbl_Schema’ of Column Type ‘Yes/No’ has an invalid ‘Initial Value’ of ‘=IF(IN(LOOKUP(MAXROW(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID]), EmploymentTbl, ID, EmploymentStatus), LIST(“PartTime”,“Unemployed”)), LIST(True), LIST(True, false))’. The type of the Initial Value does not match the column type. Consider using ‘’ instead.

When i place the expression is the App Formula, I get the same error message.

When I place the expression in Valid if, I do not get an error message and when the condition is met, “No” or “False” is no longer an option in Question7, only “Yes” or “True”. This kind of works because Question7 is a required field and when “No” is removed, there has to be a resolution before you can move on.

I tried rewriting the expression to (see below) placing it in the initial value

IF(IN(LOOKUP(MAXROW(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID]), EmploymentTbl, ID, EmploymentStatus), LIST(“PartTime”,“Unemployed”)), True, False)

I do not get any error messages, but the expression does not work. I’ve tried rewriting every way I can think of and am just not finding the answer. What are your thoughts and suggestions?

MultiTech

I’m confused, isn’t this what you were wanting? They can only select that one option in that scenario???

Your final revision of the formula looks like it would work; did you check that the lookup(maxrow)) thing is working right?

In the expression builder, just test the individual parts:

try the maxrow, see if it finds the right record
then wrap it in the LOOKUP - see if it grabs the right column data
then put it all back together.

apocunningham

You are correct, sorry about that. I did end of changing the expression a bit further to make the only possible selection be “Yes” if the conditions are met and the only possible selection be “No” if the condition is NOT met, see below.

IF(IN(LOOKUP(MAXROW(VPRAITbl, VPRAIDateEntered, [ID] = [_thisrow].[ID]), EmploymentTbl, ID, EmploymentStatus), LIST(“PartTime”,“Unemployed”)), LIST(True), LIST(false))

I am so sorry that I did a poor job of explaining myself. I REALLY appreciate your assistance with all this. I have a few more fields where an expression such as this will be helpful in reducing mistakes.

I need to create a document report for cases we assess each day for Bond Hearings. If you know of where I could find simple directions for creating a Word Document report I would appreciate it? in my first version of this app, I accomplished this in a very “Round About” way and want to do it properly.

Thanks for everything!!!

MultiTech

No worries, happy to help.

In regards to a Word doc template, all I can do is direct you to the documentation on it.