Process with parent row

Netanel yosef

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.

Brian_Richardson

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.

Genevieve P.

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

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

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.

Netanel yosef

@Brian_Richardson Hi

Thanks, i will try it !

Brian_Richardson

@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.