Process with parent row

Hello,

i have a sheet with all quoatation i sent to client and some of them moved to status "approved"/"void". the process is after i get the PO, and after i finish the job that the client buy from me i forward request to my worker for issue a invoice.


i have the summary for all quotation i sent where now is it (waiting for PO, waiting to finish the job, waiting for invoice etc.)

the problem start when sometimes the approved quotation should pay with milestones... for example:

PO about 6,000 that paid with 3 times (first4,250, second 875and last 875). when all children row will be filled(mark as yellow), the parent row will be empty (mark as green) so the next process will display that i need to issue invoice 6000 while i already did it in children rows.

**the rows "partial payment No 1/2/3" are children of "TRSL 03+05(SUP) - Piping LLM - Q#1098"

i want auto solution... now i just update it manually every time.



Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓

    You could add a column called "Invoiced" and another column called "Invoice Me" as flag type columns and use a formula to tell yourself what to invoice.

    On children, check the Invoiced column if you have invoiced the customer.

    In the "Invoice Me" column, use a formula and set it as a Column Formula so you don't have to remember to add it for the future. Use a formula like:

    =IF(COUNTIF(CHILDREN(Invoiced@row), true) = 0, true)

    This formula will put a flag in the "Invoice Me" column for any row that either has no milestones/children, or where those milestones haven't been invoiced.

    If you check the "Invoiced" box for any milestones/children, then the "Invoice Me" flag for the parent will turn off.


    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Netanel yosef

    If you're looking to see the Progress, you could count how many of the child cells are filled versus the number of child cells in total, something like this:

    =COUNT(CHILDREN()) / COUNT(CHILDREN(Description@row))

    This would give you 33% if only 1/3 child rows have data. To be more accurate, another option would be to SUM the values in the "Approved Work Value" child cells and divide that by the total value:

    =SUM(CHILDREN([Approved Work Value]@row)) / [Approved Work Value]@row


    Note that your [Approved Work Value] column would need to be storing the values as numbers, not as text, in order for it to SUM the values together.

    See: SUM Function / CHILDREN Function

    Is this what you were looking to do?

    Cheers,

    Genevieve

  • Netanel yosef
    Netanel yosef ✭✭✭✭✭

    Hi @Genevieve P.


    Thanks for your answering.

    your sulotion is good but not what i looking for.

    when the children row will be complete, the parent row will be 100% done and the "progress Value" will display that i need to issue an invoice while its wrong beacuse its already invoiced by the children rows.....

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓

    You could add a column called "Invoiced" and another column called "Invoice Me" as flag type columns and use a formula to tell yourself what to invoice.

    On children, check the Invoiced column if you have invoiced the customer.

    In the "Invoice Me" column, use a formula and set it as a Column Formula so you don't have to remember to add it for the future. Use a formula like:

    =IF(COUNTIF(CHILDREN(Invoiced@row), true) = 0, true)

    This formula will put a flag in the "Invoice Me" column for any row that either has no milestones/children, or where those milestones haven't been invoiced.

    If you check the "Invoiced" box for any milestones/children, then the "Invoice Me" flag for the parent will turn off.


    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Netanel yosef
    Netanel yosef ✭✭✭✭✭

    @Brian_Richardson Hi


    Thanks, i will try it !

  • Brian_Richardson
    Brian_Richardson Overachievers

    @Netanel yosef How did it go? Did that work, or something similar? If so please "accept" the answer that worked for you so your thread gets marked as Answered.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!