Automatically fill in values based on any column filled.

[Kaiz]

Hello Everyone,

I have a table called "Inventory", inside which contains columns [Product Category, Product Name, SKU, Barcode], all of these contain values for a single product where [Product Name] is the key.

Now i have another table called "Item QTY Data", inside which contains columns [Product Category, Product Name, SKU, Barcode, QTY]

I want to create a form for "Item QTY Data" where Appsheet automatically fills in [Product Category, Product Name, SKU, Barcode] if the user fills in any one of the four fields.

For example: If the user filled in [Product Name], [Product Category, SKU, Barcode] should be automatically filled based on the "Inventory" table. Or If the user filled in [Barcode], [Product Category, Product Name, SKU] should be automatically filled.

My problem is that if i used initial values for this, it would only work on the first try, as in after I change from wanting to fill in [Barcode] to wanting to fill in [SKU], the [Barcode] initial value formula wouldnt work anymore. I want this to be able to work no matter how many times the user wants to reset their choice, it should overwrite whatever value they have before in the textbox.

What I'm trying to achieve is similar to using App formula, but also allowing for user input if no other columns are filled.

[Ryan_Mortimer · Accepted solution]

I am not 100% certain, but I believe that Suggested Values instead of initial value will do what you are asking.

The other option would be to double your columns, and then use an ISBLANK() to show and hide certain columns?

ie. Off the top of my head (and this may be a terrible suggestion) - you could have SKU A(dereference), SKU B(user entry) & SKU (final result), where the formula for SKU (your final column) would be if SKU A is empty, use SKU B, if SKU B is empty, use SKU a - etc.

Then you just set your SHOW_IF as:

IF SKU A is empty, then SHOW SKU B
As SKU A is a dereference, it will show if there is a value and will not show if there is not a value

Again, this may not be the best way, but it is a starting point

[Kaiz]

Hello,

I've Implemented your solution and it worked like a charm, I used two columns instead of three like you suggested. i.e. I used [SKU] and [Temp SKU] where [Temp SKU] is to collect user input, [SKU] is set to a formula where it is automatically filled in if either [Temp Product Name], [Temp SKU], or [Temp Barcode] is filled, and it will only show if [Temp SKU] is empty.

It looks something like this

If i filled in [Temp SKU], everything other column gets filled and "replaced" by its original variant, which is uneditable.

Even though this means there is twice the number of columns in the backend, and not the cleanest solution, it works as it should be!

[Ryan_Mortimer]

Hey @Kaiz,

This is the fun part of the community space here, sometimes all you need is a solution to get moving and keep you inspired.

I am a big believer that there is always a better or different way to approach things with AppSheet, so keep at it and if you find a cleaner way to do this don't be shy to post your journey!

Mainly it helps other people when searching for similar things.

If I get time in the near future I may check back with some other alternatives.

Happy to hear that this solution got you moving at the very least!

[Kaiz]

Thank you for the reply, ill try and implement it and see where it goes.