Skip to main content
Question

Table that records Monthly Values of column in another table.

  • May 20, 2017
  • 0 replies
  • 69 views
Forum|alt.badge.img+6
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
      This topic has been closed for replies.

      Can you clarify. Are you just looking to record a single number each month, or the total inventory by product type, each month?
      Forum|alt.badge.img+6
      • MarkCorcoran
      • Author
      • Registered
      • May 21, 2017
      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!
      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.
      Terms & ConditionsAccessibility statement