10 Replies Latest reply on Dec 5, 2011 3:38 PM by BatuhanGizer

    Conditional Formatting based on another field

    BatuhanGizer

      Title

      Conditional Formatting based on another field

      Post

      Hey everyone,

      I know about conditional formatting but I couldn't achieve something.  I have a payment plan database, and payments can be in 3 different kinds of currency.  The currency is chosen with a drop down list when the price is entered by the user; "$, €, JPY".  There can be several payments for a specific product but if the original price is in $, then the payments will be in $.  

      So if the currency is chosen $ from the dropbox (PaymentPlan::Currency = "$") then I want $ to appear at the end of an entered value at a different field such as ; (PaymentPlan::Payment = "(user defined value" & "$" ).  But since there are 3 options, I couldn't figure out how to do it. To be more accurate;

      Product ID: 1

      Price: 1000 Currency: $

      Payment No: 1      Payment: 100 (this is where I got stuck, I want "$" or "€" or "JPY" to appear at the end of user defined value (100 in this case) according to the selection at Currency field)

       

      Thanks!

        • 1. Re: Conditional Formatting based on another field
          philmodjunk

          Why not just place a copy of the Currency field to the right of the payment field?

          • 2. Re: Conditional Formatting based on another field
            BatuhanGizer

            The problem with that is when I make a trailing grand summary, I need to show $ or € or JPY "total invoiced", "total paid" and "total balance" separetely according to currency.  So if for one product $ is chosen and € for another, at the trailing grand summary "total's" of each currency will have to be separated. 

            If you can show me a way making this possible, that would be great!

            • 3. Re: Conditional Formatting based on another field
              philmodjunk

              If you have more than one currency, you cannot have a trailing grand summary like this, but you can have sub summaries for each if you group them by currency type by sorting. Then the currency field can be placed in the sub summary part.

              If you want a "recap" at the bottom of the report with separate totals for each, you will need to do a lot more than just display the correct currency, you also need a method for computing each sub total that allows you to include in the trailing grand summary. Are you able to get separate totals for each currency type in your report? If so, what method did you use. (We can probably use the same method to produce a calculation field to display the currency symbol.)

              • 4. Re: Conditional Formatting based on another field
                BatuhanGizer

                Well I didn't prepare a report yet but I was hoping there was a way to summarize each currency type separately rather than sorting by currency.  If there is no way of getting total's of each currency separately after data entry, I will write under this post when I make a report.  

                But the way you told me about sorting with currency type might work partially for me now, since I will still get a separation by currency type. 

                • 5. Re: Conditional Formatting based on another field
                  philmodjunk

                  It's possible to get what you want, but you'll have to do some more design work to make it happen.

                  Here's one way you can use if you have FileMaker 11:

                  Go to Manage | Database | Relationships, click the table on which you are basing this report, then click the duplicate button (two green plus signs), to make a second occurrence of this table.

                  Link these two occurrences like this:

                  Yourtable::anyField X Yourtable 2::anyfield

                  Put a one row portal for each currency type to YourTable 2 on your trailing grand summary. Give each portal a portal filter filtering by a specified currency type. Put a summary field from YourTable 2 inside the portal to display the total funds for that currency type. Since you have a portal for each currency type, you can add layout text next to each to display the appropriate currency symols.

                  Here's a typical portal filter expression that whould only show the total dollars:

                  YourTable 2::currency = "$"

                  You can make your portal filter more sophisticated in order to total up funds only for a sub set of your entire table. You could, for example, set it up so that the user can specifiy a date range and the portal filter can filter out all records not in the specified date range.

                  This is not the only option, you can also set up a related table where you have one record for each currency type and use it in relationships and a portal to put this recap into your sub summary part.

                  • 6. Re: Conditional Formatting based on another field
                    BatuhanGizer

                    I will try what you wrote tomorrow Phil thanks.  I just have another quick question for you.  When I do sort first for currency type (according to dropbox valuelist) then inventory ID "JPY" comes up separately, as I wanted, but $ and € totals are together.  I don't know how it did separate JPY and combined $ with €.  Any suggestions?

                    • 7. Re: Conditional Formatting based on another field
                      philmodjunk

                      Is this a field of type text or number? Don't quite see how that would keep this from working for dollars and Euros but still separate out JPY (Japanese Yen?), but only thing that comes to mind here. It should be of type text.

                      • 8. Re: Conditional Formatting based on another field
                        BatuhanGizer

                        I couldn't think of another problem as well until you wrote.  I think I figured out.  I don't know if this is the problem but, $ and € were figures but when I wrote them as DOLLAR and EURO in the value list, it seems to be working for now :)

                        • 9. Re: Conditional Formatting based on another field
                          philmodjunk

                          If, on the storage tab in field options for this field, you change the default language to Unicode, it sorts as it should when you use the symbols for dollars and euros.

                          • 10. Re: Conditional Formatting based on another field
                            BatuhanGizer

                            Thanks! And for the report, I will get back to you tomorrow!