You have four different identifiers in use here: Total_UDBE_Amount, Amount_Contract, Percentage_UDBE, Percentage_UDBE_2nd
Which are actual fields?
I think this will serve: let ( pct = Total_UDBE_Amount / Amount_Contract ; Case ( pct ≥ Percentage_UDBE_2nd ; TextColor ( pct ; RGB ( 144 ; 22 ; 22 ) ) ) )
But I could easily be making some wrong assumptions here.
The field I'm actually trying to calculate is the Percentage_UDBE
In order to calculate this I need to divide Total_UDBE_Amont by the Amount_Contract. This gives me the percentage I need.
Since I have up to 5 contracts which all have a Percentage_UDBE ( Percentag_UDBE_2nd.....Percentage_UDBE_5th), I want the greatest position number to change the textcolor to green and all the rest to go to red.
I appreciate all of your help
I think it'll be easier to do with your current design if you just use conditional formatting on each field instead of within the calculation fields' definitions.
You might also consider replacing your designated fields (Percentag_UDBE_2nd.....Percentage_UDBE_5th) with a table of related records for greater flexibility.
Thank you I think that I am headed in the right direction with the related tables now. I was duplicating tables in the relationships editor screen and it was frustrating me. I have just discovered that when you do that it does not make another table of related records. Right?
So anywhere that I am using repeating fields I should just make related tables, correct? Even if the repetition is like 25 times?
The relationships graph displays table occurrences rather than tables. These then specify a specific data-source table in the Tables tab or in an external file. This can be confusing--especially since you most often have table occurrences with the same exact name as a data source table. See the link below if you want to learn more about this subject.
With regards to your 25 repetitions, you'd make each repetition a different record but link them to your parent table with some common value in your match fields. Thus, you have one relationship, that can link a record to any number of related records. If you haven't already done so. Look up "portal" in the filemaker help file for some introductory reading on how to set this up.
Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:
Table vs. Table Occurrence (Tutorial)
Okay but you cannot enter data into portals right? So what would be the use of not having repeating fields if I need info entered? Portals are like window right?
Nope. The whole purpose of using a portal is so that you can view and edit the data in the portals. You can even use the portal to create new related records just by entering new data in the last row of the portal if you set up your relationship accordingly.
Phil's response is correct, but the "Nope" up front is a little misleading (sorry Phil).
Yes, you CAN enter data into portals. You can create records, edit them, enter data into them and delete them through a portal.
Portals are like an 'open' window. Data can flow in and out... ;)
Ninja, thanks for the assist. Good point on the clarity aspects.
Do you have a sample database that you could send me that included some portals and relationship like we are discussing so I could look at them and figure all of this out? I find it much easier to learn when i have something I can look at from the actually program besides a book.
I'm trying the books to although I bought FM 9 the missing manuel and have read many articles people have pointed me towards but I am a hands on learner.
Here's one I uploaded to a share site a short time ago. It's intended to demonstrate a Join table, but it uses a portal for doing so. Take a look at it and see if it makes sense. Make sure you double click the lines in the relationship graph and note how the options selected enable you to add new records to the portal.
ufortunately I cant access that site here at work so I'll have to down load the file at home and then send it to my office tonight.
Thank you, somehow I feel that database was made just for me. hahaha, worked well simplified my problem and got the light to flicker.