Limit Lists search results

[Gustavo_Eduardo]

Hello good day, I am trying to make a form where dependent values ​​can be selected.

I have a table 4 tables each with its Id:

1. Items

2. Work

3. Clients

4. Progress

The Progress form takes the values ​​from the first three tables and uses them.

When I have to select a work, create a column of type ref with the following validation:

SELECT(
                Items[Id Item],
                                            IN(

                                                   [Id Item],Works[Work]

                                                   )=TRUE

               )

What I'm asking is basically "return me the Id Items found inside the Works table in the "Work" column (which are the same Id Items).

The Works table has a customer column. In the Progress form I also have a customer column.

What I have to do is limit the search, that is, not only return the items that are in Works but also, the Work that I choose in the Progress form belongs to the client that I am choosing and not that it is choose all without distinction.

Right now, with the formula I'm showing you, all the jobs in the works table are selected, but I have to match the clients I'm choosing.

Thank you very much in advance for your answers.

[Joseph_Seddik]

SELECT(items[itemID],
   IN([itemID], [customerID].[Related Works])
)

[Gustavo_Eduardo]

thanks joseph, i will try and tell you!!!

[Gustavo_Eduardo]

Hi Joseph! I hope you are having a very good day.

I'll show you the structure of my tables is, so maybe you can understand what I want to do.

In the progress table,

Customer Name is a ref of Customer Table

Once the client has been selected, the worksites that belong to this client are displayed.

Work Site is a ref of the Work Site table

Once the Work site has been chosen, Items Family that are being executed in that place are displayed.

Items Family is a ref of the Items Family table

Once the Item Family has been chosen, the Works associated with this Item Family that are executed in the workplace should be displayed, but the entire list of Items is being displayed regardless of the workplace.

Then if I choose the Item, the description can be selected but it doesn't help me to choose all the Items, I only need those associated with the workplace.

I have tried what you suggest but probably, since you don't know my tables, you don't know how to relate them.

I send you a greeting, thank you very much in advance

[VOLODYMYR_TYROL]

Please read this article https://help.appsheet.com/en/articles/961554-dependent-dropdown ,

you have to use  [_THISROW].[]

SELECT(
                Items[Id Item], [_THISROW].[FAMILI ID] = [FAMILI ID]

               )

[Gustavo_Eduardo]

Muchas gracias voy a intentar mañana te cuento!!!! Buenas noches

[Joseph_Seddik · Accepted solution]

Hola Gus,

First, as you probably know, Label columns are only for display and cannot be used in references, so to avoid any confusion, these are the significant columns in your tables, from your image:

• Customers: Customer ID
• Item Families: Family ID
• Items: Item ID, Family ID
• Work Site: Work Site ID, Customer ID
• Work: Work ID, Customer ID, Work Site ID, Family ID, Items ID
• Progress: Progress ID, Customer ID, Work Site ID, Family ID, Items ID

In your image you mentioned ".... Name" columns in your references several times. If you mean that this is what you see in the form, it is OK. But if your ref expressions are looking for values in these column then it is NOT OK and the referencing will not work. Only key columns are considered in referencing.

Now, to your Progress table. These are the expressions to use in Suggested values, and Valid if fields in your columns:

• Work Site ID:
    [Customer ID].[Related Work Site]
  
  
• Family ID:
    SELECT(Work[Family ID], [Work Site ID] = [_ThisRow].[Work Site ID])
  
  
• Items ID:
    SELECT(Work[Items ID], AND(
      [Work Site ID] = [_ThisRow].[Work Site ID],
      [Family ID] = [_ThisRow].[Family ID]
    ))
  
  

However, 

I noticed that your Work table has "Related Progress" column. It seems that you are registering progress for each work and for this you have a "Work ID" reference column in your Progress table. In that case you only need simple dereference expressions for the three columns, so the expressions above should instead be:

• Work Site ID: [Work ID].[Work Site ID]
• Family ID: [Work ID].[Family ID]
• Items ID: [Work ID].[Items ID]

And you would put these as App Formula directly.

Moreover,

Unless you are registering several record of progress for each work, like a log, you don't actually need a separate table for Progress, your can rather just add a new "Progress" column in your Work table.

Also, if you need to maintain a separate Progress table, you don't actually need the three columns above that are just a copy of the corresponding ones in Work table, except for display purposes in a dedicated view for example. 

[Gustavo_Eduardo]

You do not know how much I thank you, with a simple expression you hit the key. Joseph, always giving me the solution. Thank you very much friend. It was solved perfectly using this expression:

Item ID:

SELECT(

                 Work[ItemsID], AND(
                                                         [Work Site ID] = [_ThisRow].[Work Site ID],
                                                         [FamilyID] = [_ThisRow].[FamilyID]
                                                       )

                   )

This is what fixed the problem, now I can move forward with the app.

Regarding the progress table, if it is to register a progress per day for each work and since each client can have several worksites and each worksites several works, I had decided to make dependent drop-down lists.

I send you a big hello from here!!!!

[Joseph_Seddik]

Welcome my friend! A big hello back from España to Argentina 🙂

I just have one question:

You have a "Work ID" ref column in your Progress table, right? 

[Gustavo_Eduardo]

Yes Joseph, that's right, I have a work Id column in the progress table, but it is referenced with the items table, not with the works table. The formula takes the work records as you suggested, but those are initially referenced by the items table. If you speak Spanish, next time I'll write to you in Spanish!

Greetings

[Joseph_Seddik]

Gracias. 
En la tabla Work, ¿de dónde viene la columna “Related Progress” por favor?

[Gustavo_Eduardo]

de progress Joseph

[Joseph_Seddik]

¿y cómo se calcula por favor?

[Gustavo_Eduardo]

REF_ROWS("Progress", "Works")

[Joseph_Seddik]

Entonces, en la tabla Progress, debería haber una columna tipo Ref apuntando a la tabla Works. 

¿Cómo se llama esa columna por favor?

[Gustavo_Eduardo]

Lógico, se llama description

[Joseph_Seddik]

En este caso, será suficiente y mejor utilizar el segundo grupo de expresiones en mi respuesta arriba. Así:

• Work Site ID: [Description].[Work Site ID]
• Family ID: [Description].[Family ID]
• Items ID: [Description].[Items ID]

[Gustavo_Eduardo]

Genial Joseph! voy a intentar y te cuento muchas gracias. Que tengas muy buen día.