ERP - Edit stocks

Ask for Help
1

Hello,

I’m contacting you because I want to use Glide to build an ERP and track my stocks.

I have a project with several tables, including :

MP: A table listing all my raw materials needed to manufacture my products.
PI: A table listing my assembled products (so PI = an association of several MPs).
ACC: A table listing the accessories that go with my products (power supplies, instructions, etc.)
PF: A table listing the final SKUs (with associated EANs). A final SKU characterises an assembled product packaged with its accessories (so PF = PI + several ACCs).

In each of these tables, a row represents each item (so each ACC row represents an accessory), and the columns represent the quantities for each accessory, for example.

I have several PIs, with different MPs for each PI.

I’m looking to trigger production:

→ If I trigger a production run of 20 PI parts, this should therefore remove 20 parts from each MP associated with my PI (the MPs are associated by relationships with the PIs so, for example, the MP contains a relevant PI column) and add 20 PIs (from the product I’ve just assembled).

I can’t carry out this type of operation.

It’s quite simple to create an action that increments 20 POIs; actions with Increment numbers make it easy.

However, I can’t manage to count down the 20 MP.

My prediction is that the relationship that associates my PI with my MPs is a multiple relationship (because I need several MPs to make a PI). If I change my relationship column to a simple relationship, I will be able to use the Increment Numbers, but that does not do what I need.

I made a small diagram if it can make it easier to understand. I think this use case could apply to a lot of people, so I tried to clarify this as best as possible.

Support_GLIDE
Support_GLIDE2626×1334 118 KB

Do you have a solution to this please?

2

A raw material can be used in many finished products. And a finished product can used multiple raw materials. This is a many-to-many relationship and this relationship has attributes, namely quantity. I think I would use a join table MP-PI alongside your MP and PI tables to represent this in your database architecture.

Same goes for the relationships between PF and PI, and then PF and ACC. I feel like these are many-to-many relationships that might need attributes, and you would therefore benefit from join tables.

I would work with row IDs of course. The many-to-many relations would be created in the join tables.

I can’t really see much further than that, but that’s how I would start.

3

Thanks Nathan.

I have indeed associated relationships between my tables, but what would be the advantage of having a dedicated table that performs relationships such as MP-PI?
My main problem is related to the counting of raw materials when manufacturing several units of a single PI. How can this improve my problem?

4

First, I find join tables useful for many-to-many relations. I find the relation easier to visualize and I dislike having CSVs of children in the parent table.

Second, when a relation needs needs further attributes, like a date or quantity that describes the relation, the join table is a good way to do it.

According to AI:
(notice “When to use”)

Aspect Simple Relation Join Table
Definition Direct link between two tables Separate table to connect two or more tables
Usage One-to-one or one-to-many relationships Many-to-many relationships
Structure Foreign key in child table Contains foreign keys from both related tables
Data Redundancy Lower Higher
Flexibility Less flexible More flexible for complex relationships
Performance Generally faster for simple queries Can be slower due to additional joins
When to Use - Direct associations between entities
- When one entity belongs to another
- Hierarchical data structures		 - When items in one table can relate to multiple items in another table and vice versa
- Complex relationships between multiple entities
- When relationship itself has attributes
Examples - User profile linked to user account
- Blog post linked to author		 - Students enrolled in multiple courses
- Products belonging to multiple categories
5

Great, thanks!

Would you have any videos to recommend on the subject? I don’t see anything on YouTube about Join Table. I didn’t know anything about this principle or how to set it up, for example.

6

Is this always 20 though? Like are there any cases that one PI would require 2+ MPs?

Do you need to reduce the related ACC rows as well?

7

@davidoctavio - Interesting project! I’m in something similar where I require many-to-many relationships between machines and spare parts!
I will continue to pay attention to the recommendations of the experts!

8

This one is excellent.

2 Likes