Calculation between two tables without simple links

[phaum1967]

Hi Everyone,

I m trying to calculate revenues for each employees with two tables:

First one is Invoices with ClientID, Qty, UnitPrice, totalAmount (but I've no info about people and more tha one employee can be billed on the same invoice and many time we offer hours or discounts)

Second one is timetracking with

ClientID, Qty of hours , PriceRate (which is sometimes not the same as in Invoice as we can discount it).

My idea was to count (for each of the two tables) quantity by UnitPrice for each client. 

Then i can calculate breakdown of PriceRate for timetracking and link it to Invoice in order to dispatch revenues(TotalAmount) for each employee

Is it possible to do so in PB?

thanks for helping

[v-sihou-msft]

@phaum1967

Is the "Qty" in invoice table same as the "Qty of hours" in second table?

As you said you can offer hours for many times for one client, how did you map different unit prices into second table? I assume you suppose to have task/event id columns in both tables?

Can you share some sample data?

[phaum1967]

Hi,

Yes Qty tracked are hours in both cases but there are diffrent as in one I track all times and in the second, I invoice a part of hours (some hours are offered).

No I ve no task or event id in the two tables. My only relation possible is ClientID.

Sorry I can t share because there is confidential infos.

thanks for help....