Preventing/merging duplicate records?
Hi everyone,
I have two tables interacting with one another - an Orders table and an Inventory table.
The Orders table is orders that we _make and potentially add to our inventory, not orders that customers make with us, just for some background.
Currently, I have it set up so that on Orders, someone can check "Add to Inventory" and an action fires to create a new Inventory Item with info from the Order record. However, this obviously doesn't account for when we order more of an already existing item.
I've played around with an Inventory Adjustments (IA) table as the middleman between Orders and Inventory, but I have kind of a unique situation in that the Orders and Inventory tables don't actually have a relationship, because about half the time we order brand new things that don't exist in the Inventory table at all (so there's no related Inventory Item to select when adding an Order record).
I've had it set up so that an action creates a new IA every time an Order record has a checked "add to inventory" box, but where I'm running into trouble is having an action that adds a new Inventory Item record from IA if no such item currently exists in inventory... I tried setting it up so that the field Inventory Item Name (in Inventory table) must be unique, which means that the action I set up to create a new inventory item from IA doesn't work if there's already an item in inventory with the same name, BUT I don't want it to send me an error every time that happens, since it's intentional, AND that doesn't solve the issue of having the "quantity" field in Inventory be able to summarize the IA quantities of related items, since there's no relationship between Inventory and Orders to begin with.
Am I overcomplicating things and missing something super simple here?
Basically, I just want to be able to have an action/automation that looks at Orders/Inventory Adjustments, and (if) there's an inventory item with a matching name, it adds the quantity ordered to the quantity owned, and (if not), it creates a brand new record in the Inventory table.
Any ideas and help would be greatly appreciated!!
I have two tables interacting with one another - an Orders table and an Inventory table.
The Orders table is orders that we _make and potentially add to our inventory, not orders that customers make with us, just for some background.
Currently, I have it set up so that on Orders, someone can check "Add to Inventory" and an action fires to create a new Inventory Item with info from the Order record. However, this obviously doesn't account for when we order more of an already existing item.
I've played around with an Inventory Adjustments (IA) table as the middleman between Orders and Inventory, but I have kind of a unique situation in that the Orders and Inventory tables don't actually have a relationship, because about half the time we order brand new things that don't exist in the Inventory table at all (so there's no related Inventory Item to select when adding an Order record).
I've had it set up so that an action creates a new IA every time an Order record has a checked "add to inventory" box, but where I'm running into trouble is having an action that adds a new Inventory Item record from IA if no such item currently exists in inventory... I tried setting it up so that the field Inventory Item Name (in Inventory table) must be unique, which means that the action I set up to create a new inventory item from IA doesn't work if there's already an item in inventory with the same name, BUT I don't want it to send me an error every time that happens, since it's intentional, AND that doesn't solve the issue of having the "quantity" field in Inventory be able to summarize the IA quantities of related items, since there's no relationship between Inventory and Orders to begin with.
Am I overcomplicating things and missing something super simple here?
Basically, I just want to be able to have an action/automation that looks at Orders/Inventory Adjustments, and (if) there's an inventory item with a matching name, it adds the quantity ordered to the quantity owned, and (if not), it creates a brand new record in the Inventory table.
Any ideas and help would be greatly appreciated!!