13 Replies Latest reply on Feb 24, 2010 7:21 AM by kilbot

# Calculation help (easy Im sure just confused)

### Title

Calculation help (easy Im sure just confused)

### Post

I'm trying to use a calculation with 2 functions and keep getting an error. I am first trying to divide 2 number and then apply a case statement that will change the color of the text base on one number being grater than another. This is what I have down where am I going wrong? Do I need to place something between the 2 functions?

Total_UDBE_Amount / Amount_Contract

Case (Percentage_UDBE ≥ Percentage_UDBE_2nd ;TextColor ( Percentage_UDBE ; RGB ( 144 ; 22 ; 22 ) ) )

Thanks

• ###### 1. Re: Calculation help (easy Im sure just confused)

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.

• ###### 2. Re: Calculation help (easy Im sure just confused)

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

• ###### 3. Re: Calculation help (easy Im sure just confused)

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.

• ###### 4. Re: Calculation help (easy Im sure just confused)

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?

• ###### 5. Re: Calculation help (easy Im sure just confused)

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)

• ###### 6. Re: Calculation help (easy Im sure just confused)
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?
• ###### 7. Re: Calculation help (easy Im sure just confused)
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.
• ###### 8. Re: Calculation help (easy Im sure just confused)

Howdy,

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... ;)

• ###### 9. Re: Calculation help (easy Im sure just confused)

Ninja, thanks for the assist. Good point on the clarity aspects.

• ###### 10. Re: Calculation help (easy Im sure just confused)

Phil,

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.

• ###### 11. Re: Calculation help (easy Im sure just confused)

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.

http://www.4shared.com/file/227551445/4e0f5194/Contracts_to_Companies.html

• ###### 12. Re: Calculation help (easy Im sure just confused)

Thank you,

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.

Thanks

• ###### 13. Re: Calculation help (easy Im sure just confused)

Thank you, somehow I feel that database was made just for me. hahaha, worked well simplified my problem and got the light to flicker.

Thanks again