Problem: Inventory data in a google sheet is...

[Mary_Jane_Pende]

Problem:

Inventory data in a google sheet is refreshed from an inventory extract of the company’s main system.

The app project records cannot find the relationship it had to the inventory from the previous refresh.

Same product codes and same product name – just a bulk refresh – same google sheet, but deletes all the rows below row1 header row - then imports the new data from the extract.

Am I in trouble here?

Is what I am trying to do not possible?

Help!

[Bellave_Jayaram]

This is possible- I have a few apps that work like this.

[Mary_Jane_Pende]

how do you keep a relationship to records that have been deleted and then refreshed?

[pravse]

@Mary_Jane_Pender as Reza indicated, look at how you’ve set up the keys for the tables. When data is re-exported into the sheet, the keys for rows should not change. APpSheet doesn’t keep references to specific rows. It just computes them on the fly from the values of keys (which is why a Ref column holds key values)

[Mary_Jane_Pende]

Thanks Praveen.

I have always been pretty good at designing the data bases.

I think I have found the place where I got into trouble and am in the process of correcting it.

Thank you for taking the time to help out.

[Aleksi_Alkio]

You should be able to do that. Your app doesn’t create any hidden “ref rules” behind your tables. By any chance that you have the option “Delta Sync” ON?

[Mary_Jane_Pende]

Hey Aleksi … Thanks for the idea.

I have ref rules and dependent drop downs and de-ref rules.

I had started with a stable table of the inventory product names and pictures.

Then the client wanted to use a complete extract from the corporate data base.

As the changes came thru I realize now that I got things messed up.

I went back to basic principles and started again with the refs, de-refs and the dependent drop downs.

That cleaned up the mess and everything is working OK

now.

I am going to do some experiments with refreshing the inventory database to make sure that everything remains stable.

If not I will ask them to only refresh the Inventory balance column.

I’ll let you know how that turns out.

Thanks for kicking in with the help.

mj

[Bellave_Jayaram]

In my case, the refs are either static or entirely new. For you too, it is not an issue if the keys are preserved between re-imports.

[Mary_Jane_Pende]

I think I am going to have to keep the key columns of code and product name and just let them refresh the current balance of inventory.

Any thoughts for the community?

[RezaRaoofi]

Assuming the balance of inventory is in a separate sheet (AppSheet table) than the products table, you should not have any issues; the worst thing that would happen could be the inventory having a newer/older version than products table, therefore the balance exists in the inventory table, but the item still doesn’t exist on the list of products, or the other way around.

[Mary_Jane_Pende]

Reza, the app picks up the product name from the inventory table, so when they refresh the inventory table it is like the the app records can’t find the product record in the inventory any longer – and I guess that would be true.

Appsheet has

created a relationship with the inventory record that is now been deleted .

And that is the problem I find myself with.

The order/Project records now shows a number of related records to the order and the they all have the little orange/yellow triangle beside the product name in the order – can’t find the original record.

[Mary_Jane_Pende]

oooops … thanks for your thoughts.

I am grasping at straws here.

[Bellave_Jayaram]

Can you use a vlookup sheet formula in the header row?

[RezaRaoofi]

The yellow triangle indicates orphaned records that exist on many side of the relationship, but missing from the one side; if the app picks up product name from the inventory table, then you are currently also using that table as products table; it definitely has to contain a unique ID as Key that never changes, then the order/project tables should also refer to product via that Key value and Ref columns. This model should be safe. No need to grasp at straws! if you need more help, you have got my email; just send me the column structure, or share the app definition with me, and I will take a look.

[Mary_Jane_Pende]

Thanks Bellave and Reza.

Appreciate both of you taking the time.

I am going to work on this a little bit more and see if I can resurrect the data. I will let you know how it comes out.