Table that records Monthly Values of column in another table.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I'm new to quickbase so please bear with me.

I have a table (product details) for our company inventory.  One of the fields is a formula field called [inventory value].  This field calculates the value of fields [quantity]*[unit price].

I have another table (monthly inventory value) with the fields [recording month], [recorded value], [last updated].

[recording month] - should be a date field formatted to "MM-YYYY" or similar.
[recorded value] - should be the total sum of the column [inventory value] from the product details table.
[last updated] - date of last record change.

So, I'm thinking that I need to do a query on the products details table to get sum([inventory value]) of all records, then store the result value in a variable that can them be used in the monthly inventory values table.

Then I need to see if the record Today() (formatted as "MM-YYYY") already exists in the monthly inventory value table.  If it does, then edit record with new [recorded value] - sum of query.

If it doesn't exist, then create a new record with the updated "MM-YYYY" and record the values.

Hope this makes sense.  I currently have a Access Web App working like this using data macros, I just don't know how to make the equivalent functionality in Quickbase.

Mark
Photo of Printmark

Printmark

  • 406 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
Can you clarify. Are you just looking to record a single number each month, or the total inventory by product type, each month?
Photo of Printmark

Printmark

  • 406 Points 250 badge 2x thumb
single number
Photo of Printmark

Printmark

  • 406 Points 250 badge 2x thumb
I need to record the total value of all products in the inventory details table.

Each product detail has a field called [inventory value]. it is this field that I need a total sum of across all records.

Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
well, my real suggestion is to set up a subscription report to come monthly which is has a total and then manually key in the number once a month.

The advantage of that method is that even if you were late in King the number of the inventory value will have been recorded at the exact month end and you can key it in at your leisure anytime.

But I will post back with a semi automated solution when I get a chance.
Photo of Printmark

Printmark

  • 406 Points 250 badge 2x thumb
Cool, I wasn't familiar with subscription reports.  That will work, but I would still be interested in seeing an automated solution, as i have other cases where I will need to sum and/or count table field data, then use that information in formula fields.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
It's hard to answer a general question, but one way if recording values is to make a table report or a summary report and use the "More..." button to copy the results to another table.

If you set up your report to include the Key field of the target table, it will either update existing records or add new ones.

You could set the key field, for example to be a concatenated field of product and date. Like
XYZ - 2017-12-01