What relationship to use?

[Anuviel]

Hello. It's been awhile since I was here last. Good to be back.

I have some trouble in deciding what relationship setup to use, I am also not quite sure if I am trying to solve the issue I am having in the correct way so any input and advice is more than welcome, thank you.

Setup:

Standard invoicing solution for the invoicing of lessons with the following tables:

Invoices, Customers, Line Items, Items and some others...

Line Items table contains the following fields;

Instructor_Name, Lesson_Time, Lesson_Date and some others

Usage:

Due to the nature of business, data is directly imported into the Line items table and a unique Line Items key is created to make connection to the Invoices table. Everything works without any issues in that respect. 

Question:

My question is as follows:

1. Each day there are up to 9 lessons in a day, for example, lesson start times - 11, 12, 13, 14, 15, 16, 17, 18 ,19.

2. Instructors can choose how many lessons they can give

3. We have a bonus system setup: If the first three lessons are given or the last three or both, the Instructors will get a bonus,

Example:

Instructor works

11, 12, 13 = bonus 

12, 13, 14 = no bonus

All three starting times have to be worked or all three ending times have to be worked in order to get the bonus.

- I have set up a self-join between Line_Items table based on the Lesson_Date and have the following calculation setup:

If ( _i.LessonTime = Time ( 11 ; 0 ; 0 ) ; 1 ; 0 )

This lets me know if the time qualifies as the bonus time, I have one calculation setup for each time ( might need to do a Case calc here instead of If )

My second calculation basically sums up the first, thus resulting in a 3 if all 3 first times are worked ( last times calculation is done separately )

Sum (vLineItems_Bonus_Days_Check::_f.Bonus_Slot_11:00) +
Sum (vLineItems_Bonus_Days_Check::_f.Bonus_Slot_12:00) +
Sum (vLineItems_Bonus_Days_Check::_f.Bonus_Slot_13:00)

So if there is a 3 in the record it means that the Instructor will receive the bonus for that day. 

The issue is that I do not know how to get the number of days that the instructor will get the bonus for. 

I would like to show/do the following:

If Instructor worked 11, 12 and 13 do 1, then from Invoices table sum up the ones and get the number of times that the instructor qualified for the bonus. My described setup puts "3" into every record in the Line_Items table, even the ones where time does not qualify for the bonus. 

Hope the above is clear enough, please let me know if not and I'll elaborate more as best I can.

 

Thank you kindly.

[Fitch]

I'd probably use a script to process your invoices and then create records as needed in a Bonus table or something. If you try to do all your accounting through unstored relationships, your system will get slow when you have a lot of records. Using a script also gives you more flexibility in applying the rules that you want.

[Anuviel]

Thank you for the reply. 

Yes, it seems I'll have to separate the import of records into a few tables and create some new relationships. Will look into the scripting as well.

Thank you.