A self join could match all records in the table with the same part number. And the count of those related records will tell you if there is more than one such record--which could be used in a conditional format expression to provide a highlight color. But keep in mind that all records with at least one duplicate will be the same color.
You can also set a validation ruie and or a script triggered with OnObjectValidate to catch the duplicate entry at the moment that it occurs.
Thank you for the quick response, Phil! .. I really appreciate it. Will take a shot at it. I can make a killer layout, but scripting, etc.. is just not my thing. LOL At least I am heading in the right direction to work on it. Having a dup record is okay, I just want it to add a visual aid to it. These would be the same part numbers, but have different lot numbers which would have to be listed seperately.
I can make a killer layout, but scripting, etc.. is just not my thing.
Developing in FileMaker without scripting is like rowing a boat with only one oar.
Having a dup record is okay, I just want it to add a visual aid to it.
That's what I was afraid of. Depending on how you sort your records and how frequently duplications occur, the conditional format approach may not work for you as you can get records highlighted with the same color that are not duplicates of each other, but duplicates of other records. The method I described also highlights all duplicates within the entire table of records, if you only want to see duplicates within a given found set, this method will not work.
Yes, I've been rowing with one oar for years.... LOL!!
I applied what you suggested, at least in that direction. Making a self-join table, and joining the 2 fields, part number AND WO number, then making a global container (G_HighlightContainter) with one color pixel in it. Sized it over the field to highlight with the pixel in it, then removed it. Then added this to the conditional format of that field in the conditional format of the field:
Count(Components 2::Part Number)<=1;" ";
Count(Components 2::Part Number) ≥ 2 ; Components 2::G_HighlightContainter;""
Good news: it worked beautifully. Only highlights on the one work order record with duplicate part numbers, which it should.
Bad news, as soon as I close the database, and reopen it, the highlighting goes away. (Weird) I have to reset the one pixel again into the global field and place it again on the part number field.
Is this strange? I am using both FM 12 and 12 Advanced and it does the same thing. If I scroll through all the records it's perfect, until I close the file..LOL However, other conditional formats, always work fine.
Any clue as to whats going on here?
gezz.. I am SOOO close!!
Why do you need any global field? That is not necessary for a conditional format expression which can change the fill color of a field without needing an image in a container field to do so..
Such a global container field is how to get a highlight color, however, when you are publishing to the web with IWP where conditional formatting is not supported.
If your database is hosted from another computer, then changes to your global field's value is not retained. When you close yoru client session of the database, the value in the global field reverts. You have to either set the global field from a host session or you store the data in a non global field and use a script that is performed automatically when the file opens to load the global field with data from the non-global field. To change what data is kept in the global field, you then edit the non-global field from which it is loaded.
AH, ok.. I did not know that. Yes, I have the files on a server at work. Now that makes sense. Thanks for the heads up. I'll makes some changes on it tomorrow. I didn't know the global fields on a host computer don't get saved. We are not publishing to the web, so that's not an issue. it's just used by the internal company folks.
I thought I had also tried it without any global field reference and couldn't get it to work, it just highlighted every row of that field in the portal, which is why I went back to the global field. I didn't see any reason why the "case" statement in the conditional formatting wouldn't work on it's own. Esp. when it almost did with the pixel and global field.
I'll give it another whirl tomorrow.
Thanks Phil! Appreciate all your help!
A conditional format expression is a Boolean expression. It should evaluate to a result that is either "True" or "False" with a true result specifying that the conditional format be applied. Thus, you will probably only need the first expression inside the case function, not the entire function.
And maybe it's a typo in your last post, But changes to a global field on the HOST computer are retained. The changes made to the field on a client computer are not. You may find this KnowledgeBase article on global fields helpful: http://help.filemaker.com/app/answers/detail/a_id/3604/kw/global