One more crazy date/status relationship

[coldspring]

You've all been so kind and I am going to well one more time...

I have setup a commission payment database. Everything worked well the first period (since all the records in at the time were eligible for commission. Now things are more complex. I need to do the following relationship so I can then link the Commission Payment Period to the appropriate Invoice/Order records.

Relationship to all Order records where the Customer Signoff date is less than or equal to the end date of the commission period (3/31/2004 in this case) AND where the other conditions are met (from my previous post).

I have tried the Global approach and can't index that field. I have also tried one of the date range concepts but it is not working for me either.

Any help is much appreciated...Thanks

[stanley]

Coldspring:

Detail the conditions again for us (I may have read your earlier posts, but I'm damned if I can remember them; I read 50 or so a day) and maybe we can figure it out...

-Stanley

[coldspring]

Sorry about that. Here are the two tests.

Test 1

The ICL Signoff Date (in the child) is less than the Commission Period End Date (in the parent)

Test 2 - If the invoice is after a certain date AND the order has not been processed yet for a commission AND the ICL signoff date is after a certain date then the order is eligible for commission.

All from the child - it sets a flag to "Y" or "N"

If((Invoice Max Date>TextToDate("2/8/2004") and ICL Commission Header ID<1 and ICL Signoff Date>TextToDate("2/8/2004")), "Y", "N")

Many thanks in advance...

[coldspring]

Just thought I would bring this to the top and see if anyone had any ideas on this. I think I need to set a global with the end date from the parent file and then have it set a date field that is resident on each record. Any recommendations on how to do that efficiently?