Jump to content
Sign in to follow this  
Jarvis

Colored text conditionally determined

Recommended Posts

I would like to have the text in one field change color as a result of a value in another field.

Field 1 is TASKS:

Field 2 is PRIORITY

If the priority of a task is urgent, the font would turn red.

If the priority of a task is not urgent, the font would remain black.

Is something like this doable in FM7?

Are there any plug-ins available that would do this?

Thanks,

Jarvis

Share this post


Link to post
Share on other sites

FM7 can do this using the TextColor() function. The application help shows a couple examples.

Share this post


Link to post
Share on other sites

Hi Tim,

Well, I'm not very good with 7 yet and I'm sure there are better calcs, but try this:

Add an Auto-Enter by calculation to your tasks field of:

Case(Priority = "Urgent"; TextColor ( Tasks ; RGB ( 255 ; 0 ; 0 ) ); TextColor ( Tasks ; RGB ( 0 ; 0 ; 0 ) ))

Be sure to unclick right under 'by calculation' the box that says "Do not replace existing value."

If your priority field says "Urgent", the text in Tasks will turn red but if you change the priority to something else, tasks will turn black again. Ah, the wonders of 7!! But that calc isn't very good. smile.gif

In fact, you have Developer 7, and this might be an example of using a Custom Function. Then you could just use getRed or getBlack etc. whenever you want to. smile.gif I'll have to play with that!

LaRetta

Share this post


Link to post
Share on other sites

A smaller variant of LaRetta's method above could be done with:

TextColor( Tasks; RGB( If (Priority = "Urgent"; 255; 0 ); 0; 0 ) )

Share this post


Link to post
Share on other sites

Very pretty!! smirk.gif

Share this post


Link to post
Share on other sites

The Shadow said:

A smaller variant of LaRetta's method above could be done with:

TextColor( Tasks; RGB( If (Priority = "Urgent"; 255; 0 ); 0; 0 ) )

Yes. Or try:

TextColor(Tasks; (Priority = "Urgent") * 16711680)

wink.gif

Share this post


Link to post
Share on other sites

I understood The Shadow's rendition but not this! Could you explain it for us, please? wink.gif

Share this post


Link to post
Share on other sites

I'm just standing back applauding!

Jarvis

Share this post


Link to post
Share on other sites

That number isn't even divisible by 255. frown.gif All I can think of is that I've heard there are 16.7 million colors. I'm going to play with this too.

Share this post


Link to post
Share on other sites

( x; y; z ) = 256^2 * x + 256 * y + z

( 255; 0; 0 ) = 256^2 * 255 + 256 * 0 + 0 = 16,711,680

In other words, the colors are defined in terms of base 256. So the maximum number of colors is ( 255; 255; 255 ) = 256^2 * 255 + 256 * 255 + 255 = 16,777,215, giving the 16.7 million colors.

Share this post


Link to post
Share on other sites

I've been playing around with 7 a little, trying to apply this kind of thnking to calculations... I'd like to be able to visibly compart 2 calculated values; if totalActualExpense (which is the sum of 'actual expense') ≤ totalBudgetExpense (the sum of 'budget expense'), then I would like totalActualExpense to be green or blue. But if totalActualExpense exceeds totalBudgetExpense, then I want totalActualExpense to show in red (and bold too).

The text formatting functions are just that -- they format text expressions. Can I 'text' format numbers and/or calculations?

Abbe

Share this post


Link to post
Share on other sites

Hi Queue! smile.gif

Thank you! You explained it very well and it makes perfect sense. It was right in FM Help on RGB function that red is 16711680. I didn't understand it at the time. So Fuschia is 16711935. I will never look at RGB numbers the same way again. wink.gif

Abbe said...The text formatting functions are just that -- they format text expressions. Can I 'text' format numbers and/or calculations?

It looks like this forum didn't like the FM 'less than or equal to' and it messed up your post. It did the same thing to me so I changed to the old style to display it. But yes, you can color and style numbers and calculations, even based upon other calculations. For instance:

Amount (number)

SumAmount (Summary, total of Amount)

Paid (number)

SumPaid(Summary, total of Paid)

Waived (calculation, number, 1 for yes)

And you want to display a charge in red of $15 if they have an outstanding amount. But if the fee is waived, display 0 in black bold, otherwise display 0 black plain. Create a calculation (number)B)

Case(SumAmount <= SumPaid; 0; SumPaid < SumAmount and Waived = 1;

TextStyleAdd("0"; Bold); TextColor ("15"; RGB(255;0;0)))

And yes, I look forward to its dismemberment. I have a passion to learn lean code. smirk.gif

LaRetta

Share this post


Link to post
Share on other sites

one simple question...how do you know what color 256, 256, 256 is or for that matter any numbering order?

Share this post


Link to post
Share on other sites

Hi Joseph,

Quick way ... bring up your colors - for instance, right-click select fill color then select 'Other Color'. As you drag your mouse over the colors, watch the far right box (Red/Green/Blue). Also note that if you drag to the very top it turns white and the colors all say 255. And dragging to the bottom turns color black and shows 0. smirk.gif

LaRetta

Share this post


Link to post
Share on other sites

Is there anyway to do this conditionally formatting of text in FM6?

It would sure would be nice to have this nifty ability.

Share this post


Link to post
Share on other sites

Hello S Molly T,

You'll find a number of FileMaker v6 demos which show different approaches to applying formatting (conditionally and via script etc) in the v6 demos archive at:

http://www.nightwing.com.au/FileMaker/demos.html

Look for them among those listed in the third group under the heading 'Finesse and Added Value'. It's not as straightforward as in v7, but there are some options nonetheless... wink.gif

Share this post


Link to post
Share on other sites

I'm having trouble implementing conditional colored text in a date field. I have an auditions database that displays the date for each record in list view. I'd like Monday to be red, Tuesday blue, etc. Do I need a separate calculated date field for the list layout to return the day of the week?

I've tried something like this, but it's not working:

TextColor ( Date Color ; RGB( If (Date = "Saturday"; 255; 0 ); 0; 0 ) )

Thanks in advance for any help.

Share this post


Link to post
Share on other sites

It doesn't seem to work for dates unless the calculation's return type is text.

From your post, its not clear what types and data your fields have, but I suspect the issue is there.

Share this post


Link to post
Share on other sites

Yes. It works for text. It also works for a field defined to return its result as number *provided* the field object, when placed on the layout, is formatted with the Leave data formatted as entered option selected (in the number formats dialog). But I'm afraid I have to agree that it doesn't seem to work for date results in any circumstance. crazy.gif

If you want your date calc returned and stored in date format, but you need the conditional formatting to appear on the layout, you might wish to consider using a second calc which references the first, applies the formatting and returns the result as text, then place the second calc on your layouts. If you need to perform finds on the data (eg to locate dates or ranges) you may wish to place both fields on the layout (stacked with the formatted version in front and with opaque fill) and set the Field Behaviour options for each so that the front one is accessible in Browse mode only and the back one in Find mode only. That way conventional date searches will still be possible, even though what the users see in browse mode will be a text rendering of the date.

With the release of FileMaker 7, a lot of the kind of work-arounds that use stacked fields have become unnecessary, but perhaps this is one case were you might consider resorting to some of the old 'tricks'. wink.gif

Share this post


Link to post
Share on other sites

I think your calc would work a little better formatted like this...

TextColor ( Date Color ; RGB( If (DayName ( Date ) = "Saturday"; 255; 0 ); 0; 0 ) )

Give it a try.

SR

Share this post


Link to post
Share on other sites

CobaltSky,

Thanks for the link to the demos, helpful stuff there. Yeah not as straightforward as it appears to be in fmp7 and a little kludgy. Maybe I will just wait until we upgrade to fmp7 before trying to get fancy with the conditional formatting.

Thanks again.

Share this post


Link to post
Share on other sites

Thanks for the responses, but I'm hopelessly confused. It's not the calculations themselves so much as getting them to apply to the fields I want. Is there no way to get a field to assign text color on itself, or must it always be based on a calculation of the data of another field?

Here's what I need in more detail. I have a database called Auditions with the fields Date (date) and Confirmation (text). Confirmation has a value list of "Confirmed" and "Unconfirmed".

In List view only, I'd like Confirmation to format with red text if the value is "Unconfirmed".

Also in List View only, I'd like Date to format with different colored text based on the day of the week: Monday red, Tuesday blue, Wednesday green, etc.

The first calculation I'm using is:

TextColor( Confirmation; RGB( If (Confirmation = "Unconfirmed"; 255; 0 ); 0; 0 ) )

This doesn't work "on itself". I have to create a second filed called Confirmation Color and import the value of Confirmation, but then it won't update if the status is changed.

The second calculation is:

TextColor ( Date Color ; RGB( If (DayName ( Date ) = "Saturday"; 255; 0 ); 0; 0 ) )

Since the TextColor function doesn't work on dates, does a separate text field have to be created to import the date as text so that it can then be formatted?

Thanks in advance for any help and sorry for the noob questions

Share this post


Link to post
Share on other sites

Hi Professor,

Activity must happen within one of the record's fields to force auto-enter to change. Just switching to a report won't do it. And dates can be a problem because they don't take color. You will need a Date (text) field and you might consider setting the colors before you switch to the report and removing them after you are done, all from within the Report script. Attached is a simple demo which might help you.

If you strictly use auto-enter on a field (via calculation, do not replace), the text color would switch whenever the field data is changed but you want it only to colorize when you switch to a report. Another consideration when using auto-enter colorizing on text... when you print it (monochrome), it looks different (faded) because of the color. crazy.gif I'm finding more and more than I don't colorize the text through auto-enter (although I use auto-enter for many other things). Script makes it simpler and more flexible to colorize and style text depending upon layouts, users, etc.

I have attached auto-enter to the DateTxt field anyway - just so you can see it work when you create a new record. It is a great new feature. It is unnecessary with this script - the script alone will set the field but you may want a combination of these two options so I provided both for you.

If your Users need to change the date while viewing the report, simply turn off the DateTxt Field Behaviour > Allow field to be entered. Place your regular date field under DateTxt (Align > Center-to-center). Make DateTxt background colored so the Date field under it won't show. But if a User clicks into DateTxt, Date will open for modification.

And, yes, there are better calculations and I look forward to hearing them - particularly about fine-tuning the Choose() calculation; seems I couldn't combine any further because each color was a separate selection within Choose(). wink.gif I used RGB() because everyone is familiar with that. For myself, I use the actual color number (ie, 16711680) as it is more efficient, I hear.

The portal is simply to view the data as if it were a Columnar/List report so you can see it in action. smile.gif

LaRetta

CondText.zip

Share this post


Link to post
Share on other sites

Hello all,

How do you combine this

TextColor( Tasks; RGB( If (Priority = "Urgent"; 255; 0 ); 0; 0 ) )

with

Case (

File_Login_SubSheet_Brokers::SimilarToFirstRow = File_Login_SubSheet_Brokers::AllSamePortal; "No"; "Yes")

this field is a calc field

Joseph

Share this post


Link to post
Share on other sites

Well Joseph,

How best to combine the calcs you have described depends on what result you want for what inputs. Your first calc will return text from the tasks field in red or black depending on the priority. Your second calc will return 'Yes' or 'No' depending on a match between related records.

If you are hoping to append the result of the two expressions (eg separated by a carriage return, you could simply enter them into a single calc dialog with ampersand and pilcrow characters between, viz:

Expression1 &

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Tony Morosco
      I'm a botanist, and the tables I am working with are for tracking botanical garden collections. The data represents plants in the garden, and the plants are tagged and show up in the database.  The tables I am working with were created in FMP 7, and I'd like to open them up in FMP 11 (or later.)  The system hasn't been used in years, but still has valuable information.
      One of the tables is giving me problems using the FMP convert and recover commands.
      These tables are all inter-related.  The main table is the Accessions table, which contains records for all of one kind of plant, from the same source, received on the same date.  It is basically a museum standard.
      The other tables are related to each other through this one main table.  The Species table is related to the locations table through the Accessions table. 
      (i.e.  table A relates to table C through the table B, the intermediary)  
      From the Locations table, we can't see the the species information unless the accessions table is present.
      When issuing the open command on the main table to convert the database to FMP 11, I get the message:
      "Accessions.fmp7" is damaged and cannot be opened.  Use the Recover command to recover this file. When using the Recover command from v. 11, I get another message:
      WARNING: problems were detected while recovering the database.  Please review the Recover.log file to see where problems were found and their severity.  The recovered file should NOT be used going forward; copy only the most recent work from it into a backup copy of the original file. Recovery results:   File blocks: scanned and rebuilt 563 blocks, dropped 214 invalid data blocks.   Schema: scanned fields and tables, 1 items modified   Structure: scanned; 1 items modified   Field indexes: rebuilt  
      Opening the recovered database, there are only three records present.  There should be hundreds.  So obviously I am looking on how to wrangle this database open.
      I've attached the log file here, as well as the database structure map.  
      The other files have converted just fine.  But since the main table won't open, we are kind of stuck.
      I can share the files with you through Dropbox or whatever, if needed.
      Please let me know any thoughts you have, either basic or advanced.  And ask for any clarifications or additional questions.   :-)  
      Thanks!
      -Tony
      Recover.log

    • By Tumma K
      Hello, All!

      I am an aspiring developer for Filemaker. The company I work with is stuck in the past working off of Filemaker Pro 4.1

      I was given the task of bringing us up to Filemaker Pro/Server 13. So far my conversion prototypes are successful but we recently had a layout issue that can only be fixed in versions 3-6 (as the file is an .fp3) I work off of a macbook while our network is all Windows 7. In order for me to repair the layouts without tampering our active database, I decided the best option is to repair a copy of our solutions off the network. Unfortunately, when I go to download the trial version of Filemaker Pro 6 off of the respected website, the file is corrupt! I've tried multiple times, with different extraction apps and in different directories.

      My question is;

      Does anyone know a place where I could obtain version 6 (or better yet, 4.0) for an OSX computer? I've looked everywhere!
       
      Thank you for your time,
      Tumma K.
    • By MrEddByrnes
      I'm hoping my question can have a happy ending. In the mid-90's, I purchased Filemaker 3. When Filemaker 5.5 Pro was released, I bought the update CD, which requires the user to either have FM 3 installed or to have the installation CD for FM 3. I've used it all these years, most recently with Windows XP Pro, and it has worked just fine. The databases I began with were long ago converted to FM Pro 5.5 databases.
       
      I'm still using FM Pro 5.5 on a laptop with WinXP Pro, but in 2013, I purchased a PC with Windows 8. I haven't been able to install FM 3 on it, therefore can't install FM Pro 5.5. I am retired and rarely use Filemaker, but I have a few Filemaker databases I'd like to add to my Win 8 machine. I don't feel it's worth upgrading FM for the sake of using a couple of databases.
       
      Has anyone else run into this situation and/or have a (possible) solution? Is there perhaps any other software that can read FM 5.5 databases? Thanks in advance for your help.
       
    • By bmill
      I am using a custom filemaker solution for medical office billing written with fp5 running on a mac with snow leopard. In addition, I have a patient management db (which I wrote) that is linked through pt. ID number to the billing program allowing transfer of some demographic information (name, DOB, etc).
       
      Other than being limited by hardware restrictions, the billing program serves our needs for now and upgrading to fp12 will take some time (and money).  In the meantime, I am upgrading my pt. management program to fp13 and would like to move new patient demographic information from the billing program ( fp5 running on snow leopard through Parallels) and the new pt management program ( fp13 running on OS X 10.9) on the same mac.   
       
      Ideally, demographic information would be entered once into fp5 and then a scipt would make the data available for fp13.
       
      Any ideas on how to make this work?
    • By randyinla
      Hi, can anyone tell me why my on-line database might have stopped allowing me to delete records?  All of my access privileges and passwords are correct.
       
      thanks!
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.