Expressions that uses data from spreadsheet i...

[Viktor]

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

[Steven_Coile]

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.

[Steven_Coile]

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), “”)

[Viktor]

+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.

[Viktor]

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?

[Steven_Coile]

@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.

[Viktor]

+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