Populate dropdown using a reference and two conditions

AppSheet AppSheet Q&A
1

Can anyone help me with this?

I have two tables :

  • Table A which is a database containing only an ID and a PRODUCT

  • Table B which has a form with an ID, a reference to table A to select the product and a DATE.

problema.png
problema.png468×156 1.52 KB

if I select today, I want my dropdown to show just product 1 and 2,

If I select yesterday, I want my dropdown to show just product 3 and 4,

if I select tommorow , I want my dropdown to show all products .

Obs. date is an actual date.

How can I exclude from the dropdown the products that are already in Table B and on the date I´m selecting in the form?

In a different application I have two tables where Table A has more columns, one of which is a Date, so I can compare that with the date I select in my Table B form with this valid if :

SELECT(table A[id], and([data] = [_thisrow].[data], not(IN([lote], table B[lote]))))

2

https://help.appsheet.com/en/articles/4575802-list-subtraction

2 Likes
3

Hi @AlexandruV

Am I wrong or you speal spanish as your main language? You can post your questions in spanish here if it’s easier for you. Now, back to your question:

As always, there are different ways to do what you want.

The products available based on date are going to change? Because you could hard code things but it’s not the best idea.

I would add another column to table A to differentiate products based on the date or relative date in your case.

Then you have to use a Valid_If or Suggested Values expression on the form of table B with a FILTER() with AND() to accomodate as much conditions as you need

2 Likes
4

thank you for your reply

but how can this help if I change the date?

can I incorporate the date condition into this?

5

I speak romanian, portuguese and english.

The products in table A never change. The only thing that changes is the date in table B.

Every day I want to insert in table B all those product just once and for example if I forgot one yesterday, I want to select this date and have that product appear as uncompleted, so I can insert it in Table B with the date I have selected.

6 
TABLEA[ID]-
SELECT(
  TABLEB[PRODUCT],
  [DATE]=[_THISROW].[DATE]
)

With this you will be able to see a dropdown with all of the products that were not selected on the same date of the current form

2 Likes
7

It works, thank you! I had table A inside select function. I wasn´t doing a list subtraction as @Joseph_Seddik wrote.

1 Like