Show if constraints for row in table

[Wallace_Service]

Hi, is there a way to use a “show if” type constraint on a row in a table. We want to stop technicians from adding service tickets (one table) under a particular customer file (another table) but still want to be able to see past info. I tried using a security filter to block the customer rows but then it breaks all of the references to the old service tickets. This would be ok in sense as we can still see all of the old data but curious if there’s a way to do this without breaking the references.

Thanks in advance!

[LeventK]

help.appsheet.com

Slices: The Essentials

Display only certain rows or columns of your table with slices.

More directly you can restrict the user’s editing access for that table as well

help.appsheet.com

Limiting Users to Their Own Data

You can use Security Filters to control which records a user can see and update.

There are various ways to accomplish the same result, so provided you can be more specific on how to prevent the users access, we can advise more in detail.

[Wallace_Service]

Thanks Levent. Here’s an example:

For a few different reasons we let technicians enter a “Not in system” customer for a short time. The form you are looking at is a service ticket. We want to stop them from using the Not in system customer moving forward but we don’t necessarily want them to stop seeing the old Not in system customer records. I suppose I could create a new slice of the customer table that doesn’t include the Not in system records but then the techs wouldn’t be able to reference the old records. When I try a security filter, it stops the customer from being used but it also breaks the references between that customer record and the associated service tickets. This isn’t the end of the world, I was just wondering if there was a method that wouldn’t break the connection.

[Suvrutt_Gurjar]

Wallace_Service:

stop them from using the Not in system customer moving forward but we don’t necessarily want them to stop seeing the old Not in system customer records.

Maybe you could try the following

In the valid_if of the [Customer Name] if you do not have any expression, you could have

OR( AND([Date]>=“12/17/2020” , [Customer Name]<>“Not in system”) , [Date] <“12/17/2020”)

This will prevent the user from entering the name “Not in system” from 17th December but will let the user view / edit previous records with that entry. The expression could be further modified based on more conditional requirments.

[Wallace_Service]

Thanks Suvrutt. I think this should work, but having trouble tweaking. From the Service Ticket table I can still pick the “Not in system” Customer from it’s ref column.

[Suvrutt_Gurjar]

Could you please provide relevant details of how the [Customer Name] column is referenced? I mean expression your are using for this column and other any relevant columns settings for this column.

[Wallace_Service]

Hi Suvrutt, here is my customers table:

And here is the referencing Service Ticket customer column setup:

Essentially, I want to be able to see old Service Ticket records referencing the “Not in system” customer row from the Customers table.

But I don’t want the row to show up as an option when I pick a customer name when created a new row in the Service Ticket table.

[Suvrutt_Gurjar · Accepted solution]

Thank you.

Is the " Not in System" also coming from the referenced table?

If so, please try in the valid_if of [Customer Name] column in the Service Ticket table

IFS([Date]>=“12/20/2020”, SORT(((Customers[Customer ID])-LIST(“Not in system”)), FALSE),
[Date]<“12/20/2020”, SORT(Customers[Customer ID], FALSE)
)

Any new records created from 20th December 2020, will not have option of “Not in System”, records created before 20th December will have the option displayed.

[Wallace_Service]

Hi Suvrutt, this worked perfectly. The only tweak I made was I switched out the “Not in system” in the equation with the key value for that row. I really appreciate the help.

I believe I understand the syntax for the most part. Do you know where I’d find documentation on the “-” syntax in the SORT(((Customers[Customer ID])-LIST(“Not in system”) part of the equation?

[Suvrutt_Gurjar]

Hi @Wallace_Service,

Yes, inserting key instead of “Not in System” was a good catch. Well done.

On your query about

SORT(((Customers[Customer ID])-LIST(“Not in system”) here is the explanation

1. Customers[Customer ID] creates a total list of all customer IDs from the Customer Table

2. LIST(“Not in system”) or more precisely LIST(" Not in System row’s key") substracts that key from the list as we do not wish that option available. We need to wrap the single key with LIST() because one cannot subtract text from list but a list from another list.

3. The SORT() simply sorts the revised list in ascending alphabetical order of customer names. Now that you have mentioned about key column being different from customer name, I believe the expression could be revised to

IFS([Date]>=“12/20/2020”, ORDERBY(((Customers[Customer ID])-LIST(“Key of "Not in System Row in Customers table”)), [Customer Name], FALSE),
[Date]<“12/20/2020”, ORDERBY(Customers[Customer ID], [Customer Name], FALSE)
)

ORDERBY() is similar to SORT() but works with only keys.

Please try with the above expression instead of the previous one.

help.appsheet.com

ORDERBY()

Sort row references

help.appsheet.com

SORT()

Sorted list items

help.appsheet.com

List Subtraction

Subtract lists to produce another containing only the unique items of the first list that aren't also in the second.

Please post back if you are looking for any more details.