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

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!

3 Likes

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…

Please post screenshots of what you’ve tried.

2 Likes

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.]
2021-09-02_07h55_47


Now, following List Subtraction | AppSheet Help Center, I have tried following:
(1) In QUOTATIONS > [Ref. Quot.] > at App Formula, Initial Value & Suggested Values:
image
(2) In QUOTATIONS > [Ref. Quot.] > at App Formula, Initial Value & Suggested Values:
2021-09-02_08h19_19
(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.)
image
Ok I make it a List
image
then I get the error:
image

no way… :_(

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

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.

1 Like

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

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


These are the Sheets:
image
2021-09-02_11h27_12


And these have been my (again) new trials:
:-1:image
:-1:image
(the Type is Ref.)
image
**

:-1:As it needs a “List”, ok I create a virtual column [Ref.] with that formula but on the Form it is not displayed:
image
image
:-1: Again, I make the Ref.Quot. a Ref. from REFERENCES, so that the Quotation form takes all the reference values. But in “Valid If”:
image
the formula works (green ok) but shows all values input as invalid.
**
:-1: The previous formula FILTER(“REFERENCES”…) I input it but in here:
2021-09-02_12h13_18
but it says that there is already a row with the reference input, which is not right because there is not any yet
image

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

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

1 Like

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’ :laughing:
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.

2 Likes

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.

3 Likes