Is there a way to have an editable reference column?

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?

It would be an interesting design challenge to take a closer look in the coming days, if you feel like sending some of the actual raw data over the wire. (excel, google sheet, etc)

At a glance, have you considered “fewer tables” and “more columns”? - ideally one table. This might make the UX and the calcs a bit more manageable.

Consider for example, that “canning” as a construct does not have an unlimited number of years associated with it - some types of data do have this N unknown depth, so to speak.

Instead of specifying the year exactly and supporting N number of them across tables, I might instead try to design a model with specific columns like “last year”, “2 years ago”, “3 years ago” etc with a hard stop after… however many years after which you would never ever eat that stuff :slight_smile: (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.

Just a quick response here, there’s no wrong or right answer so far.

I have, in fact, considered having everything in one table. When I’m collecting data by hand, I collect different data based on whether it’s leftover or new, so my natural instinct is to separate them - when working in google sheets, it is also a good idea, because google sheets doesn’t do well with blank cells. I wasn’t sure how appsheet would handle it. Except for the calculation for the number column in my Leftovers Form, I have my UX how I want it, so that isn’t really a problem. I agree that older stuff isn’t going to have additional data added to it, so there might be an idea in doing things that way, I’ll have to think about it some. I would be more than happy to share a copy of my app and worksheet with the public, if it would help. How would I go about doing that? Maybe add a url link?

here’s a good write up on how to share etc:

1 Like

Thanks! I’ll do that first thing tomorrow!

I sincerely hope that is the right url! :grimacing: