Updating data into a form with an excel import
I have created a form from an excel template and imported data from it. When I import another excel file with the same data it maps the fields and imports the data correctly by adding new rows. Is there a way to import an excel file but update the data based on a key and not import the rows ?
For eg.
Excel file with columns Product ID | Product Desc | Product Type | Qty | Rate
Step 1. created a
PRODUCTS form by importing the above excel file with base data
Product ID | Product Desc | Product Type | Qty | Rate
1 | Prod A | Computer | 10 | 30000
2 | Prod B | Laptop | 5 | 50000
3. | Prod C | Monitor | 7 | 12000
Step 2 . imported another excel file with new products into the
PROUCTS
4 | Prod D | Mouse | 4 | 750
5 | Prod E | Speaker | 6 | 1250
6. | Prod F | Keyboard | 5 | 800
Now the
PRODUCTS Form has the below Data
1 | Prod A | Computer | 10 | 30000
2 | Prod B | Laptop | 5 | 50000
3. | Prod C | Monitor | 7 | 12000
4 | Prod D | Mouse | 4 | 750
5 | Prod E | Speaker | 6 | 1250
6. | Prod F | Keyboard | 5 | 800
Step 3: Is it now possible to import an excel sheet with the below data which will update the records in the
PRODUCTS form with the changed qty and rate fields for products A, C, D & F ?
1 | Prod A | Computer | 8 | 32000
3. | Prod C | Monitor | 4 | 12500
4 | Prod D | Mouse | 5 | 700
6. | Prod F | Keyboard | 3 | 700
One way is probably to write a script as below
1. create a
PRODUCT UPDATE form which will import the data from the excel
2. Compare each record in the
PRODUCT UPDATE form with the
PRODUCTS form based on product ID
3. Update products in
PRODUCTS form where the ID's match
4. Add rows in the
PRODUCTS form where ID's do not match
5. after successfully processing all the records delete the records from the
PRODUCTS UPDATE Form
My question is : Is there built in functionality in ZOHO Creator to do this ? Or is there a simpler way than described above to get this done.
Thanks !