Need help to create forms (based on gsheet) to add/update records

[PG_MASS]

Hello All,
I’m quick new in Appsheet: which is just amazing!

I’ve started to create my first app based on gsheet and I’m struggling to achieve 1 form:
The purpose of the form is to update my inventory:
I’ve basically 3 fields in my INVENTORY table
SKU
Location
datestamp

SKU is my unique identifier and the form should work as described:
I scan SKU
I scan Location
It creates a new record in the inventory table.
up to this point it works.

What i would like to do now is :
If SKU exist, get a warning and get a capability to update the existing records ( for the given SKU, update location and timestamp)

Up to this point, I’ve been able to create a virtual column telling me where the existing SKU is store but i can’t make the final step which is to update the existing record in the inventory table ( or alternatively, delete the existing record and add the new one)

I hope someone could help

Thanks

Cheers

PG

[LeventK (Levent Kulacoglu)]

Column Constraints

Valid_If Column Constraint

[PG_MASS]

Hi @leventK,
I’m not sure what to do with the 2 articles you’ve shared:
My SKU field (which is a Key) is already setup as editable.

I guess what i need to do is to create (how?) an action which update the record is the SKU already exist.
All, Any idea how to do it ?

Thanks for your support.

[LeventK (Levent Kulacoglu)]

@PG_MASS

If SKU exist, get a warning and get a capability to update the existing records ( for the given SKU, update location and timestamp)

@PG_MASS
As SKU is the key and provided a key column value exists when scanning, you will neither be able to save the record nor you can open an existing record for editing. To overcome this, you need a small trivial:
1.) Construct a helper table with the SKU scan only, import this table to AppSheet with ADDS_ONLY.
2.) Create an Action for this helper table:

Action Name: Give a proper name i.e. Verify_SKU
Data: Goto another view within this app
Target: set the below expression:

IF(
	IN([SKU], InventoryTableName[SKU]),
	LINKTOFORM("InventoryTableName_Form","SKU",[SKU]),
	LINKTOROW([SKU],"InventoryTableName_Form")
)

3.) Goto UX, expand the details of this HelperTableName_Form view, activate Auto-Save and for Form Saved Event select the action you have created for this table.
4.) Replace your main form view for scanning items with this new helper form

With this when you scan a barcode/QR or whatever, following scan it will auto-save this form and depending on:

• If the scanned SKU is not existing, it will open the InventoryTable_Form for new data entry
• If the scanned SKU does exists, it will open the existing record for editing.

Hope it’s clear and helps.

[PG_MASS]

@LeventK

new helper form

Many THanks @LeventK !!! We’re almost done, it works perfectly
One last thing : when it’s a new SKI, How to push SKU value from the helper form to the InventoryTableName_Form ?

Thanks again for your precious

Helper form:

[PG_MASS]

inventory_form:

[PG_MASS]

HI @LeventK ,
I guess (with your precious help ) i found how to adjust it
Action to verify_SKU is as below
IF(
IN([SKU], RETOUR STOCK[SKU]),
LINKTOFORM(“Ret. Stock_Form”,“SKU”,[SKU]),
LINKTOFORM(“RETOUR_STOCK”,“SKU”,[SKU])
)

Thanks again!!
Much appreciate it

Cheers
PG