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!
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.)
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.
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…!!
Suggested values (not in App formula and not in Valid If).
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.
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.
