Daily Product Balance

[Fabio_Nabuco_Co]

Dear all;

I’m building a management app, and I’m trying to build a “daily balance” of the goods produced in each day. For this, I have created a table “Entrances x Exits” of the goods produced, so when the products are registered in the table production, they are copied to the “Entrances x Exits”, and when they’re delivered, it will deduce from amount registered in the day it was produced. Therefore, I will be able to control First in, First out.

For this, I’m trying to creat actions, which are :

in which:

And to trigger that, in the table of expedition of products:

I was able to change the value to a pre-fixed value, but not yet to change the value of columns in another table with the value of the the row that is being input.

In advance, thank you for the support.

Regards;

[Marc_Dillon]

Get rid of this:

[Fabio_Nabuco_Co]

Dear Marc, this is the dispatch of the products. It’s supposed to be subtracted of my inventory, that’s why it has the ‘-’ signal. Is there any way to subtract the value of the exits from the value of the goods produced?

Thanks for your attention and help.

Regards;
Fábio Nabuco Correia

[Marc_Dillon]

Oh, gotcha. Use
-1 *
not just
-

[Fabio_Nabuco_Co]

Marc, thank you deeply for your help.

But I still cannot manage to do the action to work.

Regards;

Fábio

[Steve]

LOOKUP() produces only a single value, so SUM(LIST(LOOKUP(...))) gets a single value (LOOKUP()), creates a list containing only that one value (LIST()), then sums the one value in that list (SUM()), which will produce exactly the value LOOKUP() returned. LIST() and SUM() serve no purpose here.

[Fabio_Nabuco_Co]

Steve, as usual, you’re extremely helpful.

Got rid of the SUM(LIST)), and got the below command:
-1*(LOOKUP(([_THISROW].[Data]), “Expedição” , “Data” , Pequenos 360))

However, it still not changes the value of the child table. I’d like the app to calculate the value of the referenced day selected with LOOKUP,minus the mother values ( which are the in the “Expedição” form.

Again, thank you for your attention.

Regards;

Fábio

[Steve]

I’ve looked over the action screenshots you posted above and do not think they will accomplish what you want.

What event will trigger the daily summary? Will someone push a button? Will a scheduled report or automation bot trigger it? Some other method?

[Fabio_Nabuco_Co]

Dear Steve,

I have two events to form the daily summary:

The entrance of the goods produced, and the expedition of those. The entrance happens only once a day, and the expeditions happens many times. Therefore, I have a positive number input once a day, and negative numbers, which will be deduced from the products in stock.
At the end of a form, it will automatically trigger an action, as below:

This action consists of a sequence of actions:

One of these is the “Saída 360”, which is the one that will trigger the change of data:

The formula seems to be accepted by Appsheet, although it is still not working.

Thank you deeply, Steve!

Regards

[Marc_Dillon]

I’ve built a fair amount of inventory tracking apps, using various methods, but I cannot understand what you’re attempting to do here. I suggest broadening your explanation of your app’s setup and how you’re expecting it to work. Explaining more about how the tables are defined, and connected to each other, might help. Also perhaps try to write a simple step-by-step description of how you intend the app to work at a big-picture level.

[Fabio_Nabuco_Co]

Dear Marc,

Probably, I have described it in such a way that’s not understandable. I’m sorry for that.

I have two forms:

• One is filled with the total production of each product [Classificação]. This form feeds both a productions table, and it will automatically fill another table, which will be my inventory [ Entrada x Saída 360].

• The other form is [Expedição], which has its own table. When this table is being filled, it will feed the [Expedição] table. In this form, when the total amount of a type of product is filled, it will show 3 columns for each date of production: the date it was produced [this information will be automatically referenced using SELECT function], the amount of products produced in that day in stock, and a column to be filled with the amount of products with those specifications being dispatched. And when this form is saved, it will automatically trigger the above actions shown in print screens. This actions consists in:

subtract from the table “Entrada x Saída 360”, the products of that type, produced in that day of the [Expedição] form, in the specified columns.

I can’t thank you guys enough.

Regards;

Fábio

[Marc_Dillon]

Hmm, that didn’t really help.

Typically an inventory app has a Table of Items or Products. What is the equivalent in your scenario?

Then they typically have a table of Transactions, or Adjustments, or Usages, or Ins/Outs, etc. This table is a Child Table of the 1st Table, with a Ref type Column pointing to the first Table. What is your equivalent?

Then, often, in the first (Parent) table, there is some column to hold the current quantity of inventory. Often, this is a Virtual Column with a SUM(SELECT()) expression. It doesn’t sound like you’re going that route though. It sounds like you’re trying to update a Real Column, every time a record is saved in the Child table. But you seem to be doing it in a very strange way that I can’t wrap my head around.

Does any of the above make sense to you, or have any equivalent in your app?

---

Fabio_Nabuco_Correia:

I have two forms:

• One is filled with the total production of each product [Classificação]. This form feeds both a productions table, and it will automatically fill another table, which will be my inventory [ Entrada x Saída 360].

What is “total production of each product”?
Forms can only be for single Tables. Which Table is this Form for? Does it cause some automated action once it is saved?

[Fabio_Nabuco_Co]

Dear Marc,

The app will be used to manage a factory. There are only a few products, which are counted at the end of the every day and input in the [Produção] form ( which feeds two tables{[Produção] and [Entrada x Saídas 360]. This table [Entradas x Saídas] is my Ins/Outs ). I have a [Pedidos] ( commercial )table, which generates the commercial transaction and will be sent to the dispatch sector. Then, the dispatch [Expedição] form will be filled with the below informations:

• Type of product
• Date of production
• Quantity of products being dispatched

This amount will be diminished from the table [Entradas x Saídas 360], which is my in/out.

It doesn’t sound like you’re going that route though. It sounds like you’re trying to update a Real Column, every time a record is saved in the Child table.

Yes, that’s what I’m trying to do. I’ll have a in table , a out table, and a child table that will be both:

1. Summed when goods are input in the Mother table [Classificação] (production)

2. Subtracted when goods are dispatched.

Therefore, here’s the sequence of facts:

100 Goods of type Jumbo are produced at the date of 27/04/2021.

They’re input at the end of the day in the [Classificação] table. This data will not suffer any change whatsoever. When the form is saved, it will automatically change the data of the other table [Entrada e Saída 360], creating a new row with the amount of goods produced in that date. I had a stock of 100 boxes of jumbo.

At the date of 28/04/2021, I had 2 dispatches of 30 boxes of jumbo each. When goods are dispatched , it will inform to the person in charge of this form that we have in stock 100 boxes of jumbo. At this moment, it will create a new row to the dispatches table [Expedição], and will automatically generate an action to subtract from the [Entrada x Saída 360] table.

When the first dispatch occurs, in the second, it will be 70 boxes, due to the first dispatch that is already subtracted. And when the second dispatch occurs, there will be only 40 boxes left.

But you seem to be doing it in a very strange way that I can’t wrap my head around.

Regarding to this, I’m not certain that I’m riding the right path. This is probably not the best way to achieve what I’m trying to do.

Deeply appreciate your attention and help in this case.

Regards;

Fábio

[Fabio_Nabuco_Co]

In other words, this is what I’m trying to do:

Instead of changing the value through action to a fixed, static value ( such as +1 or any other static value ), change it with the value of a form.

Hopefully this simplifies my explanation.

Regards;

Fábio

[Marc_Dillon]

Sorry, I’m afraid I have nothing to add. I still can’t make heads or tails of your app setup. I’m sure it is a combo of our language barrier as well as you possibly having a convoluted non-standard/non-normalized setup.

I’d suggest reviewing the existing inventory management sample apps. If you still can’t solve it, you may consider hiring someone, like myself, to take a closer look. Feel free to PM me about that.