Complex problem

[Gustavo_Eduardo]

I need to create an application to design cost analyses and breakdowns of those analyses.

Cost analyses are stored in a table called E_PROCESOS_GIII, which acts as a parent table. Then, there's a child table called E_PROCESOS_GIV, where production components are stored. These analyses consist of two types of components: inputs and other processes, resulting in a nested structure.

When composing a process for the first time, it can only consist of inputs since no other processes exist. These are referred to as "first-generation processes."

New processes can always store processes of lower generation to avoid cyclic redundancy, infinite loops, or illogical relationships.

In addition to creating productive processes, I'm interested in retrieving the inputs needed for each process from the database. For instance:

To make "flour fractionation," I need two inputs: flour and a bag (process).
To make "yeast fractionation," I need two inputs: yeast and a bag (process).
To make "salt fractionation," I need two inputs: salt and a bag (process).
To make a Vienna bread, I need "flour fractionation," "yeast fractionation," and "salt fractionation," along with water (input).

I must create them as explained. However, the purchasing department requests a list of inputs to make Vienna bread. They want to know the quantity of bags, flour, salt, and yeast needed to make a batch of Vienna bread so they can purchase them.

Therefore, once all productive processes are composed in the E_PROCESOS_GIV table, the list of inputs involved for each process should be copied to another table. I've created a table called E_INVENTARIO to store parent processes with the inputs that compose the child processes and the direct input components.

How do you suggest I approach this problem in AppSheet?

[Vincent_P]

Hi,

I'm having trouble understanding the concept of fractionnation and process for a bag. Why it's not a "Flour Bag" process made up of a flour element.
In this case, we can imagine that a process has a rank attribute, and that it is composed of processes of lower rank.
So, your Vienna bread is made up of 4 processes, a bag of rank 2 flour (itself made up of a process of rank 1 flour), a bag of yeast, a bag of salt, and a process of rank 1 water. . the rank of a process being updated when a subprocess is added, making the subprocess rank max + 1.
If you want to make a Vienna bread with chocolate chips, all you need to do is create a process with Viennese bread (rank 3) and a bag of chocolate chips (rank 2) which will this time give a rank 4 for the new Vienna bread.

[Gustavo_Eduardo]

Hello Vincent,

First and foremost, I want to say you thanks for your response and the time you've dedicated to this matter.

To clarify my previous message, let me provide an example. I purchase flour in 25 kg bags, but the kitchen department doesn't use it in this form. Instead, it's first fractionated into 5 kg bags (I refer to this as fractionation), which is a pre-production step involving the alteration of the original input. What happens is that I use the flour inside the bag, while the paper bag itself is discarded. However, for the fractionation process, I require both the flour and an additional smaller plastic bag (another input I purchase). When these two inputs are combined, it becomes an internal manufacturing process, no longer just an input but a production process, which I refer to as "fractionation."

Regarding the concept of relationships, I mean avoiding illogical connections, such as a pizza being listed as an ingredient in a fractionation process intended to produce the same pizza.

Furthermore, I've revised the initial post in an attempt to make it more comprehensible.

In response to your suggestions, it aligns perfectly with what I've done. What you refer to as a range, I call a generation, but it's essentially the same concept. My aim now is to progress towards storing the compositions of inputs for each process. Since all first-generation processes involve inputs and subsequent processes are composed of other processes, my idea is to break down the higher-generation processes into lists of inputs.

I hope this clarifies the situation. If you have any further questions or suggestions, please let me know.

Warm regards, Gustavo

[Vincent_P]

Is the maximum generation two or should we imagine a number n of generations?
To return to the example of pizza, is there a "pizza base" process and another "regina" which would be "pizza base" and ham (for example).

If the maximum generation is two, that is to say only items and sub-processes, that's fine. Otherwise if you have to reason with n generation it will be complicated, I find that AppSheet is seriously lacking in recursion.

[Gustavo_Eduardo]

Yes, I know it lacks recursion. I was thinking of an alternative. Create an inventory table, with first generation processes. Then, when pasting generation 2 processes, the inputs corresponding to the component process are copied from the inventory table to the process table. Then, and given that the process table has changed, it would send a group of supplies from the Processes table to the inventory table again and the cycle ends there.

If I need to add another process, it repeats.

[Vincent_P]

You can manage just one table with column type (process or input)

else you have 3 tables. Inventory that store the 2nd generation and contains a list of ref of process and a list of ref of inputs. Processes that store the 1st generation and contains only a list of inputs. And then your tables of Inputs.

Choose 3 tables if you want that Inputs have specific properties like the unit price for example, but you will have two lists of refs to manage. Or just two (or one) tables and you will have only one list of refs but of each ref you will must test his type.

I think you're going to have some nice formulas 🙂