Re: How to Copy and Paste a whole table every week...

Jaros · 12-06-2021 06:39 AM

Hi everyone,

Stock Take App

I have an existing Excel document, that has 52 tabs(a year) where there is a list of all the items used in a restaurant and it has their cost prices and in week 1 the user enters the opening and closing stock of each and every item. It then calculates a value for the stock on hand.

I have looked at Dynamic stock control apps and those are great but the sales in the restaurant are generated by a POS system. What we are working is for verification of the actual usage in the restaurant.

How the Excel sheet works.

Every other week except for Week 1 , the Table takes the previous week’s closing stock and makes it the opening stock for that week and then the user only has to enter the Closing stock figures. Image below.

I’m trying to make this better by using appsheet but I’m lost as to how to best do this.

I’ve created one table called Price list which holds the bulk of the information as seen below

I would usually make a form and make use of dereferences to calculate each items stock on hand and value but this will take a lot longer for the user than putting it into the spreadsheet and he might miss some of the products as he doesn’t have a list to work from.

I would like to have a list of all the items from the price list as a base to work from each week.

So I was thinking that maybe a Detail View with Quick edits could possibly work, and have 52 columns representing each week.

Is it possible change the quick edit column allocated depending on what week it is? If so where would you configure that.

Also I tried using Quick edit in Table View but it doesn’t seem to work. I see it is still in Beta. Is there anything I can do to troubleshoot as a table view might be better than a detail view.

If you guys can think of a better direction I’d really appreciate some advice?

Marc_Dillon

I think you need to take a step back and read up a bit on some data design principles. Here’s an Appsheet-specific article to start with.

Something you might think about doing here is to have a column meant specifically for accepting the input of the current week, instead of manually editing different columns per week (or creating new records). Then set up Actions or Automations to transfer that data into whatever data storage that you need.

WillowMobileSys

Yes, what @Marc_Dillon said.

Also, I would plan to have the app take care of all the processing so that, in the end, you and your users only interact with the app - no other manual steps needed. Having said that, to start using the app quickly, you may need to break the development up in pieces but you do need to have a good understanding of how the data will be structured and that may mean re-organizing the sheet so that both the app and people can work with it until all processing is encapsulated in the app.

To that end, I would NOT recommend having 52 columns to represent the pricing. I assume you need to keep each week for trending purposes? But think about Year over Year. You will want to represent the Pricing updates in rows. This will help chart trends or compare seasonal Pricing.

Because you only need to update and keep the Pricing history - not product details. I would separate the Pricing into a different table so you have a Products table and then a Pricing table. The Pricing table would be used insert the new pricing info and keep the old. You can still show the most recent Price as the active Product Price on the Product view in the app. With this approach, you also do not have to be bound to weekly updates. Simply add the Effective Date of the Price. It becomes active on that date and now you can add new Pricing info as soon as you get it - even for a future Effective Date.

How to Copy and Paste a whole table every week?