Jump to content

Text format by auto enter calc for repeating fields


wolfe
 Share

This topic is 5583 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!!!

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 5583 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.