I am creating an app to track my canning. I take inventory each spring, and add new products through the summer and fall. I have 2 tables - 1 for New Entries and 1 for Leftovers (my spring inventory). In my spring inventory I track what products I have left and what year they were processed - this helps make sure Iโm using up the oldest products and not canning more than we can use. I have it set up so that my products and year in my Leftovers Table are an enum type with a valid_if statement linking back to my New Entries table. This allows me to select a product, then select from a number of available years. I would like to have it set up so that appsheet looks through previous leftover records, finds which leftovers are set to zero, then only displays the ones that as of last year still had an amount, then, when I select a year, it shows me how many were left and allows me to edit that number lower. So, for an exampleโฆ
In Leftover table (each would be a new row, except the old amount is for illustrative purposes)
Date - Product - Year Processed - Old Amount - New Amount - where itโs located
6/3/2011 - Spinach - 2009 - 6 - 3 (from Leftovers Table)
6/3/2011 - Spinach - 2010 - 5 - 5 (from Leftovers Table)
5/30/2012 - Spinach - 2009 - 3 - 0 (from Leftovers Table - date 6/3/11)
5/30/2012 - Spinach - 2010 - 5 - 2 (from Leftovers Table - date 6/3/11)
5/30/2012 - Spinach - 2011 - 6 - 6 (from New Entries Table)
6/1/2013 - Spinach - 2010 - 2 - 0 (from Leftovers Table - date 5/30/12)
6/1/2013 - Spinach - 2011 - 6 - 1(from Leftovers Table - date 5/30/12)
6/1/2013 - Spinach - 2012 - 7 - 7 (from New Entries Table)
Ok, so I know that this is just one product, but it will illustrate my point. So, in my Leftovers Form View, I want to input today (the date Iโm taking the inventory, this might help with the search for more than zero, which is why Iโve included it), then select a product (in this case, Spinach), then have a list of years to pick from (so, in 2011 - I would see 2009 and 2010, since they have more than zero; in 2012 - I would still see 2009 & 2010, since they both still had more than zero in Leftovers last year, and 2011, since it was added in New Entry throughout 2011; in 2013 - I would see 2010 and 2011 from Leftovers and 2012 from New Entry, but not 2009 because it was set to zero in 2012.), and then the new amount from last year would display and allow me to edit it down but not up, and have those results as a new row in my Leftovers Table so I can see how much Iโm using from year to year. Is this possible and if so, how?
I had the thought that I might be able to create a new sheet in my existing table with the year, product name, and current amount, bringing in the info from my other 2 tables, but Iโm not sure how to go about it. Is it possible to have it sorted and summed by year and product?
LinkedIn
Twitter
(5 years?) Then you could sort by these columns and โalways use the oldest stuff firstโ or something. Again, not entirely clear to me yet.
