Creating, updating, and/or deleting multiple linked entities from automation

Get Help
1

Greetings all,

I’ve got a problem that I’ve been looking at for a while now regarding how to implement this workflow relating to an asset management and bookings solution.

Essentially, I want a table containing a few fields such as category, quantity, start and end dates, etc, as well as a relation to another table, which is a similar schema, except WITHOUT the quantity fields, and each row is a unique booking of a single asset.

The workflow should ideally work as such:
User enters asset category, start and end dates, and quantity. The automation then generates n entries in the asset booking table based on the quantity that was selected.

The creation of these entries is the easy part. I’ve successfully set up an automation with the following code (running from the “Hire requirements” table with the quantity field, into the “Outstanding allocations” table:

for (const entity of args.currentEntities) {
    const quantity = entity['Quantity'];
    for (let i = 1; i < quantity + 1; i++){
        const createNewEntity = await fibery.createEntity("Outstanding Allocations", { "Hire Requirements": entity.id, "Piece No": String(i).concat(" / ", quantity) });
    }
}

Where it gets tricky, is when a user wants to modify the quantity from this table. In this instance the automation will generate all the entries again. How would I allow for creation, modification and deletion of the associated records, based on the quantity field?

2

I’m curious why you have chosen to set it up like this? It would seem to imply a significant amount of duplication of data, which is generally not a good idea.
I wonder if there is a better way of achieving the same goal… :thinking:

But anyway, it seems to me like you have a couple of options:

  • you can add some fields to the respective databases to make it possible for an automation script to correctly add/remove allocations. For example, the Hire requirements table can have a formula field which is the count of allocations and another which is the difference between the requested quantity and the count of allocations. Then, if the difference is positive, the automation can create this amount of allocations, starting from the value of the count + 1.
    If you also add a formula field on the allocation database which is the requested quantity minus the piece number, then you can have a different automation, which runs when this value is negative, and causes the allocation to delete itself.
  • you can effectively do all of the above within the script, by querying the allocations linked to the request and working out how many to add/which to delete.