Discussions

Expand all | Collapse all

Table that records Monthly Values of column in another table.

  • 1.  Table that records Monthly Values of column in another table.

    Posted 05-20-2017 19:57
    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


  • 2.  RE: Table that records Monthly Values of column in another table.

    Posted 05-20-2017 21:06
    Can you clarify. Are you just looking to record a single number each month, or the total inventory by product type, each month?


  • 3.  RE: Table that records Monthly Values of column in another table.

    Posted 05-20-2017 21:16
    single number


  • 4.  RE: Table that records Monthly Values of column in another table.

    Posted 05-20-2017 21:12
    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!


  • 5.  RE: Table that records Monthly Values of column in another table.

    Posted 05-20-2017 21:32
    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.


  • 6.  RE: Table that records Monthly Values of column in another table.

    Posted 05-20-2017 21:52
    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.


  • 7.  RE: Table that records Monthly Values of column in another table.

    Posted 05-21-2017 13:56
    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