This is my first time posting, and I'm basically a newbie since I am only just now getting back into using FM since about, oh, 1995 or so LOL
So far, I've managed to figure out all of my challenges on my own, but this one is driving me a little batty - and I just feel like I'm missing something really really simple.
OK So in a nutshell, i'm building a database for project management that includes a calendar where different types of events will have colour-coding so that they are easier to see.
Normally this would be very easy, but I wanted to add in the ability for the user to be able to create custom event types and a custom colour for the event.
I created a table "eventTypeTable" that has ID (auto-enter serial), eventType (text- like "meeting", "media event" etc), eventColour (number-basically just 1-20)
In my other table "calendarEvents" I create individual events and have a field that is called eventType that uses a DropDown list "eventType".
eventType_fk drop down list is a list of the ID field of the eventTypeTable, showing secondary field only of eventType.
OK still with me?
So, the user should be able to create their own list of events and colours in one Layout using a portal to the eventTypeTable
In the calendarEvents they have a record of a new event where they use a pulldown menu to select the eventType.
in the calendarView layout, I have a series of portals and filters and stuff which I don't think I need to get in to at this point, but the key part is that i have an empty button that does nothing except uses conditional formatting to change the colour based on what the event type colour number is (well, what it SHOULD be anyway).
Now, what I need that conditional formatting to DO is take the eventType_fk number that is stored (which should just be the ID field contents), and go find the eventColour number related to that ID number in the "eventTypeTable" and if THAT number is a 1, make the background blue, or if it's 2 make it green etc.
Geesh, this seems simple enough, but for the life of me I can't get them to talk to each other...and it needs to be able to look up the colour each time because, well, what if one day they decide all "meetings" should be green instead of blue.
It worked GREAT when I used the old "getNthRecord" (fieldName, recordNumber) but that ended up being useless once the ID's stopped matching the actual records... and it's no good if later down the road something gets sorted etc anyway, that would break the whole thing, no? Isn't the recordNumber mutable? I mean, I could make a calculationField that stores the recordNumber - and then reference that instead, but I'm afraid that would just change and wouldn't trickle down into the other table.