Deduct Quantity from Oldest Timestamp Entry

[Nani]

I am having two different tables. 1st table name is BOQ for Site and It contains four columns, Description (Column Type is Enum), QTY (Column Type is Number) and Timestamp (Column Type is DateTime and its app formula is NOW() )and a virtual column named as Remaining Quantity and all these have data present in it. And another table is named as Indent Used which contains Item and Quantity Using, here Item column values are taken from BOQ for Site Table – Description Column and Quantity Using is number column type where we enter a value now where it debut the value from the Remaining Quanity Column for that particular Description, and the formula for the Remaining Quantity is [QTY]-LOOKUP( [_THISROW].[Description], "Indent Used", "Item", "Quantity Using") Now what is my problem is lets say I have multiple entries in the BOQ for Site with same Description and different QTY and different Timestamp values and when I make a entry in the Indent Used Table by selecting a Item from the dropdown and entering a value in the Quantity used it need to debut from remaining quantity only on entry with older timestamp for that Description. And is it possible to combine all the Description type in to one Description Name and QTY will be sum of all the Description type

[TeeSee1]

I am not sure what you intend to do here.

The expression - LOOKUP( [_THISROW].[Description], "Indent Used", "Item", "Quantity Using") - could return the value from any one of the rows in table Indent Used that have a matching [_THISROW].[Description] and if you have multiple rows with the same Description, you cannot predict the result.

Instead you should create two actions, one on BOQ for Site to reduce the qty specified by INPUT() and the other on Indent Used to call that action on the row with the oldest timestamp and a matching description.

See this document - https://support.google.com/appsheet/answer/11621688?hl=en&sjid=7197942957843173982-AP

Note, INPUT is getting updated with a new syntax and may want to wait a bit before you implement it. (Something I learned very recently)

[Nani]

Could you please help me with the final answer? Like what actions to be created

Team @Suvrutt_Gurjar @Koichi_Tsuji @dbaum @Marc_Dillon @SkrOYC @jyothis_m @baba_sawane , Is there any alternative solution

[baba_sawane]

Hey Nani !

Try [QTY] - Any(filter(BOQ for Site,  [Timestamp] = MAX(BOQ for Site[Timestamp] ) ) ) 

edit : forgot to add the condition to make it match the description name

Maybe somth like : [QTY] - Any(filter(BOQ for Site, AND(select(BOQ for Site[Description] , [Description] =  [_THISROW].[Item],

 [Timestamp] = MAX(BOQ for Site[Timestamp] ) ) )  )

If it doesn't work, you can try this workaround : https://www.appsheet.com/templates/Initialize-forms-with-most-recent-value?appGuidString=18b39921-b8...

[Nani]

Hi @baba_sawane 

In which table do I need to use this formula whether in the BOQ for Site or Indent Used, and also is it in Remaining Quantity Virtual column or do I need to create new virtual column in the Indent Used Table

Also Do I need to create Timestamp Column in the Indent Used Table because of this condition involved [Timestamp] = MAX(BOQ for Site[Timestamp] ) 

[baba_sawane]

Can you explain, step by step (like 1, 2, 3) , what is happening in the app right now and step by step what you want it to do ? 

I don't think the issue is big but it's the goal/objective of the system isn't clear.

[Nani]

To give you better understanding, I have taken BOQ for Site Table from another app as read only view, Now in the present app, I have Indent Using Table in the present app,

The columns in the BOQ for Site Table are Description, QTY, Name of the Site, User email, Timestamp and Remaining Quantity

(Remaining Quantity is a virtual column and it has app formula [QTY]-LOOKUP(

  [_THISROW].[Description],

  "Indent Used",

  "Item Name",

  "Quantity Using") for Now )

In the BOQ for Site Table, what really happens is that, If a engineer wants any items he will open the BOQ for Site form and Select the Description which contains items and will enter the QTY, Name of the Site will automatically fetched from the Users Table based on the Useremail, Useremail contains an appformula USEREMAIL()

Now in the Indent Used Table I have a column which has a table view, where they can view all the Indents raised by the engineer based on the User Email ,I mean the description and the Remaining quantity, and there are another two columns Item Name and Quantity Using,

Item Name column type is enum and it contains values of Description from BOQ for Site Table (So what the values appear in the table view only that values can be seen) and Quantity Using column type is number. The main reason I created Indent Used Table is what Items they have and to know what purpose they are using.

Now what I want is

1. If you see in the Image there are different rows for the same Description and having their Remaining Quantity beside the Description, now I want to combine all the description with same name and their combined Remaining Quantity

Ex. Lets say I have rows with Description values as Item 1 of Remaining Quantity 5, Item 1 of Remaining Quantity 10 and Item 1 of Remaining Quantity 20, Now I just want one row to view Item 1 of Remaining Quantity 35 (20+10+5), For that I am ready to create two virtual columns, one for Description and one of Remaining Quantity

      2. If the above thing is not possible, there are different rows in the BOQ for Site                Table for same description, now when I open Indent Using Form and the                      Select the Item Name from the enum values and enter the quantity using it               need to debut from the BOQ for Site Table Remaining Quantity for that Item               Name matching with Description of BOQ for Site of the oldest timestamp.

Ex. I am having Description values as Item 1 of Remaining Quantity 5 with Timestamp Today 9:00 AM, Item 1 of Remaining Quantity 10 with Timestamp Today 1:00 PM, and Item 1 of Remaining Quantity 20 with Timestamp Today 3:00 PM, and now when I open Indent Using Form and select the Item Name as Item 1 and enter the quantity using as 3, now it need to debut from Remaining Quantity for Item 1 with Timestamp Today 9:00 AM, So next time when I open Indent Used form and select Item 1 it need to show remaing quantity 2 of that Timestamp Today 09:00 AM

[baba_sawane]

---

@Nani wrote:

Now what I want is

1. If you see in the Image there are different rows for the same Description and having their Remaining Quantity beside the Description, now I want to combine all the description with same name and their combined Remaining Quantity

Ex. Lets say I have rows with Description values as Item 1 of Remaining Quantity 5, Item 1 of Remaining Quantity 10 and Item 1 of Remaining Quantity 20, Now I just want one row to view Item 1 of Remaining Quantity 35 (20+10+5), For that I am ready to create two virtual columns, one for Description and one of Remaining Quantity

---

To have the total, considering the timestamp you can try something like : 

sum(select(BOQ for Site[Remaining Quantity],AND([Description] = [_THISROW].[Item Name] , [Timestamp] = MAX(Select(BOQ for Site[Timestamp],[Description] = [_THISROW].[Item Name] ) ) ) ) )

[Nani]

@Suvrutt_Gurjar , Can you help me with this

[Suvrutt_Gurjar]

hi @Nani ,

I see that @baba_sawane  has been assisting you and has exchanged a few discussions with you and even suggested an expression.

My request to you will be to try his suggestion, respond back to him with your findings. I believe it may be inappropriate for me or anyone to step in till there is something obviously incorrect or a better alternative approach is available.

I will also request your to always conclude the queries you post in the community -if a solution was useful to you and if it solved , then mark it as a solution so that any future readers will benefit by reading the post thread.  

[baba_sawane]

It's great to have a community that values everyone's contributions, and your message is a nice reminder of that 🤗

@Nani it would be nice to validate/invalidate/upgrade suggestions, if several people can't figure out the issue in a single answer. A clear presentation of the issue can also make it far easier to solve 🖖

[Nani]

@baba_sawane‌‌ Yes, I agree with you 😀, but I am haven't got what I excepted, you have given me the solution with timestamp for the first condition, but I haven't mentioned that I want with Timestamp in first condition. Moreover, I have also given clear presentation along with the image

[baba_sawane]

I would more say that you gave a clear description, but the presentation is something else. Best way is to describe the issue in a logic or functional or process way more than describing it with sentences. 

For the timestamp condition, if not needed, you can remove it, and also the and() .