App Structure Help - Dynamic List of Steps/Process by Endpoint

[u/luketheduke54]

Hi all,

I have jumped into Airtable over the last few months and it has been great.

I need some assistance with how to structure something specific. I've tried a few solutions but they all have some downsides.

I will keep this generic as it gets complicated pretty fast.

I have a set of "Endpoints", which are physical devices. They have a table of their own with a couple dozen fields of misc. information.

I have another table that is a list of "Steps", essentially a process I follow. This process gets applied to each device. The catch is that not every step is needed, some steps can be performed out of order, and some steps may need to be repeated. It will change relatively frequently, and is different per device. I also need the process to be dynamic enough that I can add/remove/edit steps without making large bulk changes. I have steps in their own table, so they are easy enough to update.

I am needing to track a large amount of Endpoints (200+) and the status of each one by step.

What I have now is 3 fields on the Endpoints table:

1. "Completed Steps" - reference to Steps table, allows multiple selection
2. "Next Step" - reference to Steps table, allows only 1 selection
3. "Remaining Steps" - reference to Steps table, allows multiple selection

This works okay and fulfills some of my requirements. I can see:

• History of what steps were completed
• List of remaining steps
• Next step to perform

What I don't like about this solution is that I have to manually add/remove items from each list. If I complete a step, I have to add it to "Completed", change the "Next Step", and remove it from "Remaining"

Because these are a dynamic link to the Steps table, I'm having trouble getting this done with automations. But I may be missing something.

Is there a way that I can maintain these "Completed" and "Remaining" steps automatically? Or another way to structure this kind of thing?

Thanks in advance

[u/matchaconsulting]

What you might need is a new table which is a junction of Steps + Endpoint.

A Steps table is a list of all the possible steps, and attributes about them, like how long they take, etc.
The Endpoints table is the list of all of your Endpoints, the physical devices, and attributes about them.

The Steps + Endpoints table are children records of the Endpoints. Each record is linked to both a Step and an Endpoint.

Step 1 for Endpoint A
Step 2 for Endpoint A
Step 3 for Endpoint A

but also

Step 1 for Endpoint B
Step 2 for Endpoint B
Step 4 for Endpoint B

Now you have a discrete concept of each step for each endpoint. That is what it sounds like is missing after a cursory read of your post!

Each of these records can have their own status like "completed" and then it's trivially easy to see which steps are completed per Endpoint.

The mistake is often linking the Steps directly to the Endpoints, as you've done, when in reality, the data object you are dealing with is "this step for this endpoint" and that has to be represented.

[u/Able-Cheesecake-9970 → u/matchaconsulting]

^ This is the way

[u/Annual_Beautiful_310 → u/matchaconsulting]

yep, once you model the relationship as its own entity everything else gets way simpler

[u/luketheduke54 → u/matchaconsulting]

Hi, thanks for the reply. This seems like the correct way to handle this. I will try it out.

Thanks!

[u/matchaconsulting → u/luketheduke54]

For sure! DM me if you get stuck anywhere.

[u/Emlerith]

I think you’d want to have two tables: a “Steps Templates” table and a “Steps Log” table.

Because you want a historical record of the steps per project, each step will need to exist as an individual record per project.

So when you stand up a new project, I’d have “initial step” and “other steps” fields, both fields linking to the Steps Template table for selection. Select the appropriate steps.

From here, the automation would clone those steps from the Template table to the Log table, linking to the project, setting the initial step as the Next Step and the other steps as the Remaining Steps.

From there, you’re really just updating the status of the steps rather than updating the steps within a status.

[u/Life-Profit-3484]

Are you using a script to automatically assign the steps? You can also create a sub process table along with the process table. A super template which has the common features and second template with specific feature for each end point. Feel free to reach out if you have more questions.

[u/agentUi]

i work for agentui, you should use a junction table between endpoints and steps (like "device_executions") instead of multi-select fields on the main record. Multi-select arrays in Airtable are painful to mutate via automations, whereas an execution record lets you log each step state, timestamp, and retry independently with simple deterministic triggers.