Avoid adding duplicates and access only relevant ones

Hi, community,

I would appreciate your help with the following issues.

I have a “step” form where I need to add the following fields:
a) customer (which references to a customers table that allows me to either create new one or select from a list already added customers)
b) process (which references to a processes table that allows me to either create a new one or select from a list already added processes)
c) roles (which references a roles table that allows me to either create a new or select from a list of already added roles).

The relationship between the tables is the following:

  • a customer can have many roles; a customer can have many processes.
  • the “step” can have many customers, many processes, and many roles.

The issues I have now are that, when I’m in the aforementioned form wanting to add a process, that process is assigned to a customer. However, when I select a customer from my “main form”, instead of seeing only the processes that belong to the customer I have previously selected, I can select any process created, for all the customers. That creates a lot of confusion.
What I would like to do (and please help me with this) is:
a) to see only the processes that have been recorded on the customer that was selected in the form) and if we don’t have any process created for that customer yet, then the list should be empty and only prompt “new”.
b) same should happen for roles. I want to avoid accessing roles from other customers because as you can imagine in real-life scenarios, you can have multiple Support Consultant roles for various companies and we want to keep these separated.

Thank you!
Alina

Hello @Alina_Negulescu, what you’re looking for is a couple of valid_if() expressions, for example:

For your “Process” column:

FILTER("Process",IN([Process_Id],[_THISROW].[Customer_ref].[Related Process]))

For your “Roles” column:

FILTER("Roles",IN([Rol_Id],[_THISROW].[Customer_ref].[Related Roles]))

Let me know if they work for you.

hello - thank you, Rafael!

here is my steps table:

this is the formula that I adapted for the Process column in the data validity:
FILTER(“Processes”,IN([ID],[_THISROW].[Customer].[Related Processes]))

Here is the app After I select the customer:


After selecting the Customer, when trying to select the processes from that customer the list is empty:

Even though there are processes defined for that Customer:

Food for thought?

Hmmm, could you share a screenshot of your process table please?

I can’t see any processes there, maybe im starting to think like an app haha

You’re fast! Thank you app Rafael :wink:

The description of the fields is not intuitive at this phase so I’ll translate.
The process is under “Name” = Counting chickens.

Aah i see, im gonna insist with the screenshot of your process table structure then hehe, the formula didn’t throw out any errors right?

Here you go - “Process” Table.

Thanks!

Okay, i got a question:

Your adapted formula refers to a virtual column called [Related Processes], but if your table is called “Process”, it should be [Related Process], have you changed anything about that virtual column?

My bad - sorry. The name of the table is “Processes”

I see, could you try this expression for the process column valid if()?

[Customer].[Related Processes]

That should return a list of keys for your processes related to that customer, and since the process field is a ref, it should let you choose directly from those.

1 Like

It works!!! You’re a star, Rafael! I’ll replicate for the Roles column as well!

1 Like

I think i over complicated the expression at first, my bad hahaha,im glad we found something that works

1 Like

It’s amazing! And yes, we sometimes we tend to think more complicated than the case. Have an amazing day and a fantastic weekend, Rafael! I’m far from finished with it, so we’ll probably have other opportunities to connect soon :slight_smile:

1 Like

I have another issue now…So, for the entries that were already added in the tables, it works perfectly. It only shows me the processes and roles belonging to a certain customer.

Problem is when I add a new customer - that goes fine, but then when I add a new process to that customer, it doesn’t save it.
Any idea why?

That was probably sooner than you originally thought uh? haha

What isn’t saving, exactly? the formula we applied is for the process_ref column valid_if() field in the Step table, we didn’t touch your processes table at all i think.

Please check your spreadsheet for the entry that didn’t save, it might be lacking a reference, if that’s the case then you should check the edit permissions for the column responsible for that reference

[quote=“Rafael_ANEIC-PY, post:15, topic:42358”]
What isn’t saving, exactly?

/ it’s not saving the new process I’m adding under a new customer.

the formula we applied is for the process_ref column valid_if() field in the Step table, we didn’t touch your processes table at all I think.

/ Indeed. I have only added [Customer].[Related Processes] to the Process column from the Steps table & [Customer].[Related Roles] in the Role column from the Steps table.

I need more details, is it not allowing you to save the row when you’re inside the form, or it saves it, but then you can’t see the record you’ve just added? if the form saves it, can you find it in your sheet or database? if you can find it in your database, is the entry complete with the references it should have?

Sorry for bombarding you with questions hehe

Hi Rafel. I will respond in line:

I need more details, is it not allowing you to save the row when you’re inside the form, or it saves it, but then you can’t see the record you’ve just added?
/AN: When I am inside the from, it allows me to click the save button, but then for a brief second it shows an error, which I can’t see :frowning: and I can’t see the record I just added.

if the form saves it, can you find it in your sheet or database?
/AN: I can’t find it in the original sheet - so it doesn’t save it.

if you can find it in your database, is the entry complete with the references it should have?
/AN - N/A - see above

Sorry for bombarding you with questions hehe
/Np, it’s very helpful - thanks, buddy!

Hello Alina

That’s weird, any chance of you recording your screen so we can pause the video when the error pops up so we can see what it is? otherwise i think it would be a good choice to contact support@appsheet.com

1 Like

Good Idea. I’ll do that!

2 Likes