Forum Discussion

WaldenWang's avatar
WaldenWang
Qrew Member
6 years ago

inventory management

There's one parent table with field quantity which record how many items we have. Each item in parent table will have several child which also have quantity field means how many we consumed. Now I want the formula quantity of parent table - sum (quantity of child table). So I can know how many left? Does anyone happen to know how to make it? Thanks.
Formulas and functions
  • JackFretwell's avatar
    JackFretwell
    Qrew Assistant Captain
    6 years ago
    You can do this via the table to table relationship, you can bring a summary field from the child to the parent, you can also define a rule for the summary, I do something with All opportunities and Won opportunities so I can bring 2 figures from the child table.
  • JeffRichey's avatar
    JeffRichey
    Qrew Trainee
    6 years ago
    Hi Walden,

    As Jack states above, you can achieve your stated tracking objective by utilizing numeric summary fields in your table to table relationship.  My preferred technique is to use three summary fields because the value displays as report links which allow users to "drill down" to those records whereas the value produced by a numeric formula field does not display as a report link.

    My summary fields in the parent table are usually something like this:
    [Number of Records] - count all records
    [Number of Records Complete] - count all records that meet the status criteria for "Complete"
    [Number of Records Remain] - count all records that meet the status criteria for "Remain"