Globals and Calcs and Fields, Oh My!

[dtantum]

I may be over engineering this one, but I've been working on it for way too many hours and can't come up with a solution that works. The logic for this is sort of baffling, so bear with me...

To start with, I have a list of Jobs/Projects. That's the last easy part.

Each job has a Job [status] field. The Job's status may list the department a job is in, or the process that is taking place at the time (eg. On Hold, Out for OK, In Mac Dept., etc.). This is selected from a popup Value List (I'm coming back to this, don't worry!).

At some point long ago, I decided that I would design this thing for expandability, without me having to do a bunch of re-programing. Heh.

With this in mind, I basically created two repeating Global fields, Variable [status List Open], and Variable [status List Closed]. These two lists contain user entered values for the Job [status] Value List. A Calculation field, Job [Open | Closed], looks at the different job status options and determines if the job is "Open" or "Closed", setting the field with the result. How it determines this is by the following series of "if, than" statements:

code:

---

If(PatternCount(Job [status], GetRepetition(Variable [status List Open], 1)), "Open",

If(PatternCount(Job [status], GetRepetition(Variable [status List Open], 2)), "Open",

If(PatternCount(Job [status], GetRepetition(Variable [status List Open], 3)), "Open",

If(PatternCount(Job [status], GetRepetition(Variable [status List Open], 4)), "Open",

If(PatternCount(Job [status], GetRepetition(Variable [status List Open], 5)), "Open",

"Closed")))))

---

This all works fine, but where I am running into trouble, is that I have another Calculation field, Variable [status List All] that has the duty of holding all of the values from the above mentioned "Status List" fields. It generates this info by performing the following Calculation:

code:

---

GetRepetition(Variable [status List Open], 1) & "

[BobWeaver]

Well.... OK. I didn't follow completely, but I think I kinda sorta get the general idea.

First, I think the entire FM Forums will be behind me when I say "Lose the Repeating fields." They cause nothing but grief.

Why not put all your possible value list items in another file, and then set up your value list based on the field values from that file. You can create a second field in that file that indicates whether the item is an "open" or "closed" item. So, the new file has two fields: Item and OpenClosed. Now when your user selects an item from the value list, you can have a relationship to the new file that will retrieve the OpenClosed value.

Now, it's easy to add new items by simply adding them to the new file.

[dtantum]

Thanks, Bob.

Your suggestion is just what I needed... something simple and expandable. I guess I've been staring at this thing for too long!

Dustin