Select from a list to validate

Hey Everyone,

I’m trying to set a valid if constraint for a session to be created. I want the session that is to be booked to validate whether the parent table (Client Case) has a column (Status) value as active.

I have this at present:

IFS(SELECT(Client Case[Status], [_THISROW].[Ref] = [Ref]) = Active, True, False)

Problem is that my Status column is a list, so Im getting the Cannot compare list with text. After doing a little research I found that I should more than likely use the IN() function, but I cant seem to figure out how to fit it in to make it work.

Could someone please offer some advice.

Kind Regards
Ben

Hey @Ben_Rix , may i ask a few questions about your app?

If your status column is a list, does it mean it could have several values of different types? (active, inactive)

What are you trying to prevent the user from doing exactly? i see that you want to check if the client case is set as active in order to book a session, does that mean you want to prevent the user from adding a session to an inactive client?

1 Like

Hey Rafael,

Yes thats correct, Status column has a list of values, Active, On Hold, RFFT, Discharged. If the list value is not active then i dont want a session booked.

Basically, everything that you said is what im trying to do.

Kind Regards
Ben

1 Like

So it should be an Enum type column with text base format, in that case it should not be a list :thinking:

Great, we’re on the same page then, i’ve been working on an app that does exactly what we’re talking about, and the solution i went for was to manage the buttons presence (both inline and prominent) via slices.

Having your rows that are not Active on a slice that does not allow adds automatically hides every action that adds something to the table, it does not however, forbid you from adding children records to the table.

The workaround for handling those pesky children adds is to add virtual columns to the parent table with ref_rows expressions, each pointed at a slice of each child table that you don’t want your user to be able to add rows once the status is not “Active”.

EDIT: I have just realized i have not directly answered your question haha, let me know if what i talked about works for you.

1 Like

So, ive just noticed that my status column was actually a text column but had a LIST() of suggested values. Im gonna change that to enum and base type to text. and see if that has any impact.

Regards
Ben

1 Like

That list was still an issue when i created the enum column, so, i made a virtual column with a [ref].[Status] and that seemed to sort the issue out. Probs should have done that from the beginning. Ive got another one of these valid if queries I need some help on but ill post that under another question.

Thanks again for your help Rafael

1 Like

Try these, hopefully you won’t need a virtual column if one of them works:

[Reference_to_client_case].[Status]=“Active”

OR

IN(“Active”,[Reference_to_client_case].[Status])

3 Likes

Thanks so much Rafael. That did the job perfectly and I also removed the virtual column.

2 Likes