How do i calculate monthly balance for multiple product?

Question

I work in a project delivery departmentThe department source a few products from overseas and we use quickbase to facilitate the delivery and orderswe currently extract all data into excel and use excel to report the products balance each month.we have been trying hard to find a solution within Quickbase to provide a live update&nbsp; in graph format each time the product movement is updated/createdbelow is a screen shot of the graph we try to achieve in Quickbaseany help is much appreciated

------------------------------Leon Wong------------------------------

blakeharrison · Answer

The graph that you're trying to create is certainly attainable in Quick Base, but I suspect your application is not setup to allow for it. Can you share more information about how your application table/relationship structure is laid out?

------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quick Base Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------

m_a_admin · Answer

Hi BlakeI am restructuring the APP (nothing has been setup yet) so that the team can get the above sample report on QuickbaseI am able to get around using excel connection to download the usage into excel, formulate them and feed the numbers back into Quickbase in the above table format. However, this means the team can view this in live environment and this creates another manual processI have tried several times, the relationship i created is able to pick up the stock movement for each month (ie a month table that has many orders/movements) and i struggle to come up idea that is able to summarise the balance for each month for each product without potentially creating hundred of fields.We try to future proof this in the future as the department might source more products.appreciate your helpthanks------------------------------Leon Wong------------------------------

blakeharrison · Answer

Well, you're going to need to create a more complex table structure within your application. If you need to be able to see the numbers for sales Per Product, Per Month, you will need something along these lines:

Of course, this is a very simplified version, but with this structure, you would be able to do Summary fields of your sales numbers from the Line Item to the Product Month and then create the report from the Product Months table.------------------------------Blake Harrisonbharrison@datablender.ioDataBlender - Quick Base Solution ProviderAtlanta GA404.800.1702 / http://datablender.io/------------------------------

m_a_admin · Answer

Thanks Blake. your flowchart and guidance have given me some ideas&nbsp;!!!manage to work out with less tables (not sure if this concept is similar to your thought process)what i did1. Product (master) connects to product movements (child)2. summarise monthly movement into Master table (12 new summary fields)3. Create accumulated fields for each month against each product in product table (another 11 new summary fields)4.create accumulated lookup fields in Product Movement table (child) for each month&nbsp; (12 new lookup fields)5. create a formula field to workout the balance each record should pick up6. Month table (master) connects to Product movement table (child)7. Create average summary field for each product in month tablei will then get the below table on Quickbase that calculates the accumulated usage in each month for each product which is identical to Excel

------------------------------Leon Wong------------------------------

blakeharrison · Answer

While this will certainly work for you, I don't recommend it. This structure is not scalable and will require practically constant addition of new fields to accommodate new products, new months, etc. You've added tables, yes, but you're structure is still essentially flat.------------------------------Blake Harrisonbharrison@datablender.ioDataBlender - Quick Base Solution ProviderAtlanta GA404.800.1702 / http://datablender.io/------------------------------

Forum Discussion

How do i calculate monthly balance for multiple product?

5 Replies

Related Content

Multiple reference proxy fields help

Calculated field spanning records on multiple tables

Managing record access for multiple data levels

Calculate total days with a condition...

Formula Help - Average Productivity Per Day

Recent Discussions

Filter Monthly, Quarterly, Yearly from one Date field

Extrapolating nuanced data

Forms help

Share your feedback on the new Qrew Discussions

Generating QR Code with URL to record