Discussions

 View Only
  • 1.  Table to Table with math

    Posted 11-01-2022 16:32
    First post here!  Been working with QB for awhile.   

    We have a table, call it table A with some costs.   Table A has a child table, table B.   Costs are entered in table A.   There's another table, table C that has costs for the items in table B.   I'm racking my brain trying to figure how to build a pipeline (I assume this is what it needs to be, because we need table C to be the final "source" for costs) that will take the costs in table A, divide by how many child records are in table B and linked, and then create new records in table C.

    Example,  I put $100 in an allocation group (table A).  There are three sub items in that allocation group (table B).   I need my user to be able to pull an item report with costs that shows $33.33 (okay bad example with the repeating) per item.  



    ------------------------------
    Jeff Hughes
    Founder
    pointflow.co
    ------------------------------


  • 2.  RE: Table to Table with math

    Posted 11-01-2022 20:31

    Let's drop the talk of ABC's 

    So one Allocation Group has many Sub items in a relationship.

    What's  is the third table (ok Table C).  It sounds like it's Items. Do we have a relationship where one Item has many sub items?

    Are you looking for the Average Cost of the Sub Items to be rolled up to the Items Tabke?



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Table to Table with math

    Posted 11-04-2022 12:20
    Ok, let's name them.

    I have these four tables:   Buildings, Building Cost Records, Sites, Site Cost Records

    Sites to Buildings is one to Many

    So, I spend $50,000 at the Site.  Which has 5 Buildings.  I add that $50,000 to Site Cost Records.

    I want that $50,000 to go look up the buildings it's tied to, and divide evenly out to the the buildings.   So 5 $10,000 records are added to the Building Cost Record table.

    ------------------------------
    Jeff Hughes
    ------------------------------



  • 4.  RE: Table to Table with math

    Posted 11-05-2022 16:24
    OK, so a Pipeline can do this.

     But before we do that make sure that the Site has a summary field for the # of Buildings and look that up down to Site Costs.  So The site cost in your example knows there are 5 Buildings.  Create a new field called Building Allocation which will be the $50,000 / 5 = 10,000. 

    Trigger on New Event for the Site Costs table, but for now within that, only set trigger on record added.

    Search for Building where the Related Site matches the Related Site of the Trigger record.

    Then "For Each",
    Create a building cost record being sure to attach it to the correct [related building] the result of the search step (probably {{b.id}}
     and populating the field for the $10,000 Building Cost from the trigger record. 


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Table to Table with math

    Posted 11-07-2022 10:27

    Ok this sounds perfect and I follow!  I'll try it out.  Really appreciate your insight.

     

    A picture containing icon  Description automatically generated

    Jeff Hughes

    Founder

    PointFlow Companies, LLC

     

    Phone: 918-693-1835

    Email: jeff@pointflow.co

     

     

    www.pointflow.co     Title: LinkedIn - Description: image of LinkedIn icon