Form field: Show all values from a list except the...

Jewel · 08-31-2021 10:40 PM

We have 2 tables:

References => Column ‘Reference’ (type “Enum” with many values as references for the quotations)
Quotations => Column ‘Ref. Quot.’ (type “Ref” pointing to the table )

Problem:
I would like, whenever I fill the form Quotations the Appsheet, in the column “Ref. Quot.” only shows the values (from the column “Reference”, table “References”) which have not been used yet.

I.e.: I have made already several quotations, each with the references

202201 ANOA
202205 LEOPARD
202204 GIRAFFE

Next time I make a new quotation the app
(1) OR must show all the values from the table “References” EXCEPT the values that I already used (202201 ANOA | 202205 LEOPARD | 202204 GIRAFFE).
(2) OR can suggest one (1) value (from all the values of the table “References”) randomly, EXCEPT the values that I already used (202201 ANOA | 202205 LEOPARD | 202204 GIRAFFE).

I have tried and tried to search for a solution but I cannot get it, sorry… Someone can help please…? Thank you so much in advance!

Lynn

Jewel

Thanks. I have tried several combinations of “List Subtraction” but no way :_(
Any suggestion, how to write down the formula, please?

Also, I suppose the formula has to be input in “Suggested values”, is this right? (or where please?) Thanks so much once again…

Steve

Please post screenshots of what you’ve tried.

Jewel

Hi Steve,
first of all THANK YOU very much for the help. I’m trying hard and in the last month I am becomming a kind of super fan of Appsheet, I’m learning very much, so thanks so much!

First, I have the table
QUOTATIONS where the column [Ref. Quot.] is Ref. to the table:
REFERENCES, where the key is the column [Ref. Quot.]

Now, following List Subtraction | AppSheet Help Center, I have tried following:
(1) In QUOTATIONS > [Ref. Quot.] > at App Formula, Initial Value & Suggested Values:

(2) In QUOTATIONS > [Ref. Quot.] > at App Formula, Initial Value & Suggested Values:

(as trying to say "from all the references included in the column Ref. Quot from the table Ref. Quot, show me all possibilities which are not yet include in the column “Ref. Quot” from the Quotations that I have been making.
=> I have made the Type “Text” => error
(3) If I only write “take the values of the column [Ref. Quot.] (table REFERENCES) referred by [Ref. Quot.]” (not yet with the “-” for the Subtraction), it says that the type of this [Ref. Quot.] needs to be “List” (it is a Ref.)

Ok I make it a List

then I get the error:

no way… :_(

Thanks so much once again if you can help please… Thanks.

SkrOYC

This should help a lot to you I think:

Also, maybe you can use the [Related something] column that’s automatically added when using Ref columns. You could substract all the rows that already have data on that VC like FILTER(“Table with the rows you need”, (COUNT([Related something]<>0)). It’s just an idea.

Jewel

Thanks! No way again (every is a trial)…: Who can help me please?..

Actually yesterday I also tried FILTER among so many options but no way Anyway I have heard you, so I have tried:

These are the Sheets:

And these have been my (again) new trials:

(the Type is Ref.)

**

As it needs a “List”, ok I create a virtual column [Ref.] with that formula but on the Form it is not displayed:

Again, I make the Ref.Quot. a Ref. from REFERENCES, so that the Quotation form takes all the reference values. But in “Valid If”:

the formula works (green ok) but shows all values input as invalid.
**
The previous formula FILTER(“REFERENCES”…) I input it but in here:

but it says that there is already a row with the reference input, which is not right because there is not any yet

Also tried other things… no way… Thanks for everybody’s inputs once again.

Jewel

I got it!!!
In the table QUOTATIONS, in the column [Ref. Quot.] I wrote the formula SELECT(REFERENCES[References], [References] <> [Ref. Quot.],true) in Suggested values (not in App formula and not in Valid If). It stills suggest values already used but at least when I click on save the form, there is a note in red color which says that there is already that value.
Wowww… difficult, but got it. Maybe soon I will post more doubts as I am making a complex app :)) Thank you so much once again for the help…!!

SkrOYC

Great.
That’s why I reply to you inviting you to read all those help articles. I prefer to say ‘read this’ instead of ‘you are so wrong my friend’
Also, take the following advice: Name your columns on a more user-friendly way. You will be able to troubleshoot better (and will help us to do so at the same time)
Finally, I suggest you to use QREW tools extention (aka AppSheet Toolbox) if you are using a chromium-base browser.

Jewel

Wowwww THANK YOU VERY MUCH!! I really appreciate your help. I am a person who usually helps but is not used to receive/ask for help. So thanks so much. That Toolbox seems amazing! I have already installed it and looking forward to using it. Thanks ALL and please take care of yourselves.

Form field: Show all values from a list except the used ones