How can I generate a report with seperate Batch ID's

[Nani]

I have 2 different tables

Inward and Outward

• Inward

Inward Table serves as my designated space for documenting incoming items. It features a deck view with various columns. Whenever an item arrives, I open the form, record the details, and fill in the corresponding columns. Please find the below attached image for columns

• Outward

Outward Table is just like a requirement form, the people working at my place, request some items which I got from Inward Entry and I will be giving them by approving it, now here I only enter the item and its quantity required. Please find the below attached image for columns (Need to debut the quantity from Inward Table for that Item Quantity they requested). Let say they have requested for something as you see in the below image

Here if you for all the items they have requested 200 quantity, but how it should be divided is ,lets say Inward Table, the first batch ID: FTH6951822 has 100 nuts it should be taking 100 nuts from this Batch ID and it need to take next 100 quantity of nuts from another batch ID: GHT694196, if this batch id doesn’t have the quantity wanted then it need to take all the quantity it is having for the batch id and item and need to move to next batch id and it should be continuing like that (Need to debut the quantity from Inward Table for that Item Quantity they requested)

Now want I wanted is when I generate a report for Outward Table, it should be same as below image

After making the entries in the Outward Table, the Inward Table should become something like this

Please compare first and last image for your understanding

Please help with the proper solutions. Thank You

[Nani]

Team @Suvrutt_Gurjar  @Koichi_Tsuji  @dbaum  @Marc_Dillon @SkrOYC  @jyothis_m  @baba_sawane @AleksiAlkio  @jaichith  @Julio35 , can you help me with this

[baba_sawane]

Hi Nani,

Why listing in the same row item 1 , item 2 and item 3 ? Are they linked in any form ? I think it would make things easier if there were 1 item/row and you can just remove an inward row whenever the quantity gets to 0.

Other question, did you already built automations and have trouble creating the template ? 

[Nani]

Thanks a lot for replying @baba_sawane 😀

Hi @baba_sawane ,

Yes, it is form type, because it is a single table. If it is better to have 1 item/row, then I will be creating Parent and Child Table.

 Yes @baba_sawane , I have created the template. But I stuck at Outwards Table, like what formula's should be placed in Batch ID and in Mfg Date because they just enter the Item, they wanted and its quantity

Please find the below attached image to get better understanding

For Better Understanding here is the example:

Here if you for all the items they have requested 200 quantity, but how it should be divided is ,lets say Inward Table, the first batch ID: FTH6951822 has 100 nuts it should be taking 100 nuts from this Batch ID and it need to take next 100 quantity of nuts from another batch ID: GHT694196, if this batch id doesn’t have the quantity wanted then it need to take all the quantity it is having for the batch id and item and need to move to next batch id and it should be continuing like that (Need to debut the quantity from Inward Table for that Item Quantity they requested)

[baba_sawane]

Hi Nani to answer your dm, 

There are workarounds to achieve what you want, of course. Someone just replied to your post while I was writing, but I still can give you a few directions.

As mentioned, you will need create another table that will be filled only through  action looping.

This is what I can think of :

You can add a virtual column "oldest batch of the item" to your indraw table, then get this value in a new physical column in your outdraw table (that you can also name "oldest batch"). 

And also add a column "QuantityGot" in the indraw. Reference it to the new withdraw table, for this outdrawID.

Then loop the actions using these values. If QuantityGot > Quantity required, then , action to add a new row to the new table ("withdraw")  where you can add the values of the final report. 

In the same loop of actions, update the values of the oldest batch. 

You can refer to sources meNtioned by @SkrOYC to achieve this. 

(for reference you can use the reply button to answer posts)

[SkrOYC]

I'm full on developing these days but I'll let you know one solution and it's overview.

You have two different needs, please approach them separately, although being aware that reports usually tell you wich fields you will need to have in the first place.

The automatic outward items assignment

You will most probably need a loop (in other words, that's what I would do) so that when someone asks for items, you fire a loop that is going to take items from the inward table following the FIFO method.
This also means that you most probably will need to create another table so that you separate the "request" (input from the user) from the actual items (taken by the actions).
Please refer to these videos for more details about what we are talking about
Looping with Actions (Do-While & For Loops in AppSheet) | BUILD VIDEO - YouTube
Action Looping - As Easy As 1-2-3 || AppSheet Explained - YouTube

This way AppSheet can take the items in sequence.
Please be aware that this is an advance method to use, but needed in your required use case IMHO.

The report

As mentioned above, you will need items to be children of the outward table. So you can have an "outward request" and "outward items" or similar and fields to link them using their corresponding key values. You can then create a section where it shows what the user asked and how the final list of items was taken based on their requirement, displayed in a table.

Please forget about showing items in columns, that's a bad habit created by worksheet suites