Conditional formatting is exactly what you need for this.
Right-click field 1 in layout mode and select "conditional formatting".
Click the Add button to begin a new conditional format.
Change "Value is..." to "Formula Is..." and click the Specify... button.
Enter the following expression:
table::field1 < table::field2
At the bottom of the dialog select Bold and Underlined text styles.
Return to Browse mode and try it out.
That is SO close! Now I realize I actually want this:
If Field 1 is equal to or between Field 2 and Field 3 then it should be bold/underline.
For example: Field 2 is May 11 and Field 3 is May 15 - I want field 1 to be bold/underline if the date in that field is equal to or between those dates.
Is that possible?
table::field 1 > table::field 2 AND table::field 1 < table::field 3
Note that for "table" you need to use the name of the table where you defined these fields. I am also assuming that all three fields are in the same record. If not, then the expression may get a bit more complicated.
Thanks, I did realize that it has to indicate the table I am working from and yes, all the fields are in the same table. But for some reason now nothing is being bold/underlined. Before I added the parameter for Field 3 there were fields that met the condition and were being bold/underlined. When I add the AND info for Field 3 - nothing works. And I can see at least 10 dates that fall within the parameters. It certainly looks right. Any suggestions?
One thing I should mention. These fields are not all in the same record, but they are in the same layout and the same table. The issue is, we have many projects, all due at different times. But we need to look ahead a week at a time to determine what is coming up. So I want my administrator to be able to enter dates in Field 2 and Field 3 (beginning and end date) and then I want field 1 (the dates already inputted for that project) to be bold and underlined if they fall within those dates. I am not sure if that makes a difference but I wanted to give you as much information as possible. I don't understand why it worked with only part of the equation but not when the next step is added. And rather than use the word "and" I used the "&". I have actually tried it both ways. If I use "&" some dates are bold but not the right ones. If I use "and" no dates are bold. I have tried everything I can think of. It just seems so simple - and maybe that is the problem.
I appreciate your help and patience.
First of all "&" and "AND" do completely different things. Don't use "&" in this case. The & symbol "glues" to text expressions together into a single line of text and that's not going to work.
Are field 1, field 2, field 3 all the SAME field but in different records? That makes a huge difference and will keep my previous examples from working as expected.
If they are the same field, how does the user tell them apart? In otherwords, how are the beggining and end dates different from the "dates already inputted from the project". Please provide more detail and answer these questions. Then we can try again.
Ok, hopefully I can give you all the information you need, in the proper terms. I am new to FileMaker so unfortunately my lingo may not be the same.
1. I have a table called Production Schedule.
2. In that table I have many fields including (a) Walk through (b) Beginning date (c) Ending date. These are all Date Fields and none of them are indexed.
3. I have a layout called Production Schedule which includes fields (a-c) all within the body. The Walk through date is entered manually for all records.
4. I want the admin to be able to type in dates for (b) and (c) and it will change the formatting of (a) if it falls within those dates. That way those records are easy to spot among the hundreds that we have.
I hope that helps. I apologize if my knowledge is just not capable of explaining the situation accurately. If there is a better way please let me know. Thanks!
When you are looking at these records, do you restrict all the entries to a specific project so that you have:
Record with beginning date
Record 1 with walk through date
Record 2 with walk through date
Record with ending Date?
(The order of the records aren't critical but the location of the dates are.)
How do you distinguish the records from different projects. (To format your date fields, you need to be able to access the date in a specific record's field, Identifying that one specific record out of many is the challenge here.)
In the layout called Production Schedule I actually have 20 fields, including the book name, book number, etc. 17 of the 20 fields are date fields indicating specific deadlines within the process (walk through, book review, proofs due, etc.). So I can definitely tell which record I am talking about. I just want to see all the records that have a walk through date that falls between specified dates. But I need to be able to tell FileMaker which dates I want to use as parameters.
In the layout called Production Schedule I actually have 20 fields, including the book name, book number, etc. 17 of the 20 fields are date fields indicating specific deadlines within the process (walk through, book review, proofs due, etc.).
And all these date fields are 17 separate records in a list view or portal?
So I can definitely tell which record I am talking about.
Yeah but Filemaker can't read your mind to tell one record from another. Since I can't see your database file, I'm trying to find out what identifies your records as records for "War and Peace" as opposed to records for "Brothers Karamazov". It sounds like "book number" is the field I'm looking for.
When you open File | Manage | Databae | Relationships, what tables and relationships do you have? We'll need a relationship to set up the correct expression for your formatting and you may already have one that works. If not, we can create one, if I can get a clear enough picture of how you've structured your table(s) and Fields.
Here's what I think you've got judging by what you've written so far:
For each book you have somewheres from 15 to 17 records. One or possibly two records contain the start and end dates. If we can reference the correct record in our expression, we can set up a conditional format by comparing the "walk through" date in the current record to the "start" and "end" dates of the record or records where a user typed in these dates.
Have I interpreted your posts correctly? This is critical or my assumptions might lead you and I right off a cliff.
I sincerely appreciate your patience. It looks like you need some more background information. I hope I can answer your questions:
The separate date fields in this layout are portals. Each book is a record.
When I go to manage the database I have a table called catalogs that has many fields, including these 20 fields. That table has a relationship with my schedules table. These are linked by the book name. The layout I am working pulls information from both of these tables via portals. The common denominator is the book name.
How can I tell FileMaker that on the layout I am working with, bold and underline any dates that are within a certain range.
So your interpretation of my posts seemed correct, it is my fault I was not giving clear enough information. I hope we are getting closer for your sake. I can understand your frustration and I apologize.
I'm not the least bit frustrated. I'm just being very careful not to give you bad advice. This thread started out looking like a simple issue and became much more complicated when we "popped the hood" and took a look at the engine. :smileywink:
I Gather your "walk through" date is in the schedule table. Are the start and end date fields in the schedule table or the Catalogs table?
If you have Catalogs::Startdate, Catalogs::Enddate and Schedules::WalkthroughDate then this is easy to set up. If they are all in Schedules, which makes less sense to me, then things get more complicated. Let's assume that you have the start and end dates as part of Catalogs and they're on the layout proper, outside of the schedules portal.
Then your conditional text expression will look like:
Self > Catalogs::Startdate AND Self < catalogs::EndDate
"Self" is a way to say "that object called me" (Schedules::WalkthroughDate in this example) without spelling out its name.
I think we are still missing something. I was trying to send you a screen shot but that isn't going to work. I did try what you had below and it didn't work. The equation itself makes complete sense to me. Could it really just be a matter of placement? But I did what you suggested and the start and end date are part of the catalogs table, so my formula looks just like you have below. But still no go.
I am a little confused as to why it would be more complicated if those fields are in the schedules table. In my mind they are basically just floating fields where data is entered that is used for this conditional formatting (or calculation). I envision it much like an excel spreadsheet. Make Cell C bold if it is between the dates in Cell A and B. Also, could you explain what you mean by outside the schedules portal?
I feel like we are so close, don't give up on me!
You're right we must be missing something, but it's hard to find the missing piece through this forum. You can post screen shots to the forum, but you have to upload your screen shot to a web page somewhere else and then use the little "tree" button to post a link to the hosted image. If you can figure that out, it might save a lot of posting back and forth.
I suspect that your relationship between catalogs and schedules is not what I expected. When you open the portal setup dialog for your portal, (Double click it in layout mode), what table is named in the "Show related records from:" menu in the top of this dialog? When you look at Layout Setup... for your layout what table is named in the "show records from" text box?
When you look at Manage | Database | Related Records you should be able to find a table occurrence (a box with the table's name) for each of these and a line that links the two tables. Since you have a portal that works for you, the conditional format calculation should work also.
When you look at the start and end date fields in layout mode, do you see "StartDate" and "EndDate" or do you see "::StartDate" and "::EndDate"?
"I am a little confused as..."
Unlike spreadsheets, calculations in Filemaker most easily reference other fields in the same record. Since the start and end dates are in a different record, Filemaker needs a way to know which record has the values we want for a given calculation in a specific field. The relationship that makes the portal work should also make the conditional format work.
Spreadsheets specify a column and a row in their calculations and this allows those calculations to refer to any cell in the worksheet. With a database, the field name gives us the "column" but we have to use a relationship match to figure out which "row" contains the number we want.
Hope that makes sense.