Avoid adding duplicates and access only relevant o...

Alina_Negulescu · 03-19-2021 02:30 AM

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

Rafael_ANEIC-PY

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.

Alina_Negulescu

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?

Rafael_ANEIC-PY

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

Alina_Negulescu

You’re fast! Thank you app Rafael

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

Rafael_ANEIC-PY

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?

Alina_Negulescu

Here you go - “Process” Table.

Thanks!

Rafael_ANEIC-PY

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?

Alina_Negulescu

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

Rafael_ANEIC-PY

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.

Alina_Negulescu

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

Rafael_ANEIC-PY

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

Alina_Negulescu

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

Alina_Negulescu

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?

Rafael_ANEIC-PY

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

Alina_Negulescu

[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.

Rafael_ANEIC-PY

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

Alina_Negulescu

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 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!

Rafael_ANEIC-PY

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

Alina_Negulescu

Good Idea. I’ll do that!

Alina_Negulescu

@Rafael_ANEIC-PY I managed to record my screen and capture the error:

This is from the Steps form - after I add a new process which should be displayed here.
However, if I am adding the process from a different form (derived from the Processes table) then it will save it and I can use that process from the Steps form.

I probably described it as more complicated than it is. Let me know if I can clarify it further.

Rafael_ANEIC-PY

I think i understand, so you create a new process directly from the steps form, once that process is created it is auto selected by the “Process” column, but what happens is that [Customer].[Related Processes] has not yet updated for this new row, since it is a virtual column.

POSSIBLE SOLUTION: Move your [Customer].[Related Processes] expression from valid_if() to suggested values, and give it a test.

Edit: That happens because when you add a row for a parent table from within a child, both rows are then added in succession once the parent form is saved, so the app isn’t aware yet that you added a new process, so it blocks it.

Alina_Negulescu

Makes sense, buddy! And it works now. You’re a star

Alina_Negulescu

I have another one - on the same topic but related to roles. While it only shows me the roles belonging to a customer, which is great, I can create two roles for the same customer, with different wages. The problem is, I don’t want this to happen, because it’s difficult to select then from the list, when adding a new step, the one you actually need. How can I prevent adding the same role name for a certain customer?

Thank you - I’m forever in debt!

Rafael_ANEIC-PY

If there’s only going to be one role per customer, you could just pull it from the table using the reference, so you don’t even have to ask your user about it in your step form, that would require that it is previously filled in, of course.

If you want to have the option to add another role to the list from within the step form while limiting it to a single role per customer it can be tricky, since in my experience you can only control the “add” button managing table or slice permissions, so that would be a pain to execute.

Let me know which option interests you hehe

Alina_Negulescu

So - a customer can have multiple roles, with unique names. Now it allows me to add duplicated names

Rafael_ANEIC-PY

For that you’re going to need to add a validation in your role table, so that it checks, given a customer, if there are other roles with the same name, something like this:

ISBLANK(FILTER("Roles",AND([Customer_ID]=[_THISROW].[Customer_ID],[Role_Name]=[_THISROW].[Role_Name])))

Avoid adding duplicates and access only relevant ones