Expressions that uses data from spreadsheet if it exists:
Hey guys - I hope you can help me with this one. I need an expression that checks if a column has data inside it, and if it has, it should โloadโ that into the form, and if not, the column should just be empty and open for user input.
For instance, the user types in an order number, and if a row with this order number exists, it should load the other columns into the form for the user to edit (for instance company name, slogan or other details). So far Iโve tried with something like this:
=IF(ISNOTBLANK(ANY(Select(Data[Company], [Ordernumber]=[_thisrow].[Ordernumber]))), ANY(Select(Data[Company], [Ordernumber]=[_thisrow].[Ordernumber])), โโ)
This seems to work fine with loading, in this case the company name, BUT, it doesnโt allow me to input a value myself if no value is present. Any suggestions?
Best regards
Put the expression that pulls existing data into the columnโs Initial value field. Note, however, that the data will only auto-populate when the row is first added.
Your expression:
=IF(ISNOTBLANK [Ordernumber]=[_thisrow].[Ordernumber]), ANY(Select(Data[Company], [Ordernumber]=[_thisrow].[Ordernumber])), โโ)
should instead be:
=IF(ISNOTBLANK([Ordernumber]), ANY(Select(Data[Company], [Ordernumber]=[_thisrow].[Ordernumber])), โโ)
or:
=IF(ISNOTBLANK([Ordernumber]), LOOKUP([_thisrow].[Ordernumber], Data, Ordernumber, Company), โโ)
+Steve Coile Thanks for you reply. Unfortunately it should be so that when the user enters the order number, it loads the rest of the columns in the form, so the user can see it and edit it. Again, this isnโt a problem - The problem seems to be making it able to auto-populate and still be open to input depending on the value of the order number column.
I didnโt see your second comment, for some reason it was marked as spam. However, I donโt think it is what Iโm looking for, because [Ordernumber] will never be blank. What Iโm trying to achieve is, that the user can write an ordernumber that doesnโt exist already, and that way create a new entry in the sheet, but if the ordernumber exists in the sheet, it should โloadโ that row into the form. Does it make sense?
@Viktor Salling Iโve tried but not been able to achieve what youโre attempting (โthe user can write an ordernumber that doesnโt exist already, and that way create a new entry in the sheet, but if the ordernumber exists in the sheet, it should โloadโ that row into the formโ), so Iโm afraid I donโt have any guidance.
+Steve Coile Thank you for your attempt. Although, it does seem like a simple thing, so Iโm surprised that itโs not working out. It seems like when you use the IF formula to โload inโ a value, itโs not possible to enter a value yourself. As in: If ordernumber exists in the sheet, load the company name from that row, if not, let the user enter a company name. I even set the value to be โblankโ if it couldnโt find any ordernumber, and then the editable_if column to โtrueโ so that the text could be edited, but it was still not editable
User | Count |
---|---|
43 | |
29 | |
29 | |
14 | |
14 |