We have 2 tables:
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
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!
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.
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.
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.
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.
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โฆ!!
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.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |