Text format by auto enter calc for repeating fields

[wolfe]

You guys always seem to come through when my brain starts to freeze up like my computer so here goes.

I am working on a simple (?) work schedule for up to 20 employees. I have a file with 13 layouts, 1 admin and 12 months to show the calendar months.

I've created 366 fields showing the day of the month, ( M, T, W, T, F, S, S ) which autopopulate after the first day of the year is chosen. I have a script to add Feb.29th for leap years and recalculate the days. So far so good.

Next I want to create a display of work shifts for my 20 employees. What I had in mind is a repeating field named "Jan1" with 20 reps. This way I set this field below the "Day1of366" field on the layout and I can set different work shifts and off days for each of the twenty employees using the repeating field. Still so far so good.

The problem is greed...I want to show schedule shortages by turning the text red when a person uses a sick day or takes overtime or for some unforseen reason takes off work. I also want the text to be blue when the person takes off for a scheduled event, like their regular off days or a scheduled vacation, so anything that is listed in the "codes" field will show either black or blue, any other entry will be red.

What I have so far is on the admin layout I have a repeating field that stores the schedule "codes", like 1 for first shift, 2 for second P for a personal day and such and soforth. This is a repeating field

My autoenter calc is a "get repetition" calculation which looks something like this,

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 1); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 2); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 3); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 4); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 5); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 6); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 7); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 0) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 8); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 255) );

If ( GetRepetition ( Jan1; 1) = GetRepetition ( Codes; 9); TextColor ( GetRepetition ( Jan1; 1); RGB ( 0; 0; 255) );

TextColor ( GetRepetition ( Jan1; 1); RGB ( 255; 0; 0))))))))))

This works fine for the first repetition, but how in the world do I address the remaining 19 reps?

[Ender]

A work schedule is one of those things that really should be done with a relational structure. The trouble with this flat structure is that even if you figure out this current calc, you'll face the same issue again when it comes time to total the work hours/days, or you want to have a list of the individual employees' scheduled hours/days. Then what happens if the business grows, and more employees are added? It sounds like you'd have to add more fields.

So instead of going down this repeating field path, I'd suggest you rethink the structure, going with a relational setup using multiple relationships, or a single relationship and multiple portal instances. These types of scheduling structures have been discussed in the past so you should be able to find something similar to what you're trying to do.

[comment]

This thread might give you a start.

[wolfe]

Well I guess I just had to step away from the tail pipe of my car for a while??? I think I've solved this by not even addressing the fact that the day fields, "Jan1", "Jan2" etc... fields are repeating fields. I tried this and the calc works with all the occurances in the repeating field;

If ( Jan1 = GetRepetition ( Codes; 1); TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 2); TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 3); TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 3) ; TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 4) ; TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 5) ; TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 6) ; TextColor ( Jan1; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 7) ; TextColor ( Jan1 ; RGB ( 0; 0; 0) );

If ( Jan1 = GetRepetition ( Codes; 8) ; TextColor ( Jan1; RGB ( 0; 0; 255) );

If ( Jan1 = GetRepetition ( Codes; 9) ; TextColor ( Jan1; RGB ( 0; 0; 255) ); TextColor

( Jan1; RGB ( 255; 0; 0) )))))))))))

I did look at the information posted and appreciate the responses you guys gave. Using the above calc will allow me to address my colored text for any number of repetitions.

Ender, you had a very good point and since the above calc works (so far) seamlessly, I'm changing the repeating fields to 50 or so to allow for expansion. I will still only show 20, but this way if expansion is needed, it won't be a problem

Thanks for the input!!!

[wolfe]

Hold the presses!

Now it seems the only field to work is Jan1. It appears Jan2, Jan3, etc... do not work. All the options are identical, I've even tried duplication and copy n paste, but I can't reproduce the working field, any ideas???

Edited August 16, 2006 by Guest

[wolfe]

Okay, I've spent several hours trying to figure this thing out. I don't get it. The "Jan1" field works fine, but I can't reproduce it. I even tried creating a new file and using the same field names, copy and paste didn't work and duplicating didn't work. I even tried to copy and paste from the layout and then rename the copied field....Nada!