Changing colour when duplicate record

[Enigma20xx]

I have a portal and I want to change field's colour when a record is duplicated and I can´t make it.

i.e.:

States.

New York

California

Texas

California

So should be

New York

California

Texas

California

Thanks to all.

[LaRetta]

To identify if a record is a duplicate within the same table, you need to use a relationship.

Duplicate the table occurrence of your portal table in the graph. Join it as:

MainTable::State = MainTable 2::State

Then attach conditional formatting the state field in the portal with:

Count ( MainTable 2::State ) > 1

And then below, specify text red.

[Enigma20xx]

Thank you very much LaRetta. Works perfectly.

Only one thing:

I had to make the table occurrence with two fields because if not, it shows red fields that don't have a duplicate in the portal but in the table.

Thanks again. Works fine.

[LaRetta]

Aha, you were filtering the portal or the original relationship. Makes sense. I'll know to verify that possibility in future.

[comment]

To identify if a record is a duplicate within the same table, you need to use a relationship.

I used to think so, too...

DuplicatesInPortal.zip

[LaRetta]

:shocked: LOL. It is amazing how simple it is in concept when shown but how difficult for the mind to come up with to begin with. I am constantly surprised and amazed.

In this case, there is additional filtering (portal or relationship) so that would need to be taken into account in your portal filter conditional format calculation also, correct? Nice example, Michael.

Never mind ... the calc uses the same relationship as the portal. DOH, :laugh2:

Edited March 2, 2012 by LaRetta

[Enigma20xx]

I am making a few combinations in another layout to visually understand data by colours. And the combination is not so easy, lol

Lets say:

I have a table with employees.

In one day they work, leave and some of them come back to work again.

MainTable:

RecordID

...

LinesTable:

EmployeeID

Name

StartTime

EndTime

LinesTable – RecordID and EmployeeID – LinesTable2:

I have formatted the fields and the background becomes red when not IsEmpty ( EndTime ).

So I can see who is not at work.

What I’d like to accomplish next is, to detect if there is a mistake entering the data:

i.e. Peter worked and leaved at 10:00. Back to work at 13:00 and still hasn’t gone out, but someone has entered him again at 14:00. If that happens I’ll format text blue, for example.

Doing what you taught me works but detects the line that has and EndTime too, so gives false result.

I don’t know if I made a great mess trying to explain myself, lol.

Wow I din´t see last two posts and made another question.

You two are too fast for me. Let me understand things first lol.

[comment]

1. What is the "Main Table"? What does a record in this table represent in real life?

2. It looks like you are actually looking for records that overlap, is that correct? Why does this need to be in a portal?

3. Why is your message in a font smaller even than the forum's default?

[Enigma20xx]

Starting by the end:

3. I think I don't manage very bad in english but is not my mother language. So I first use my word processor to write my posts (to mini-mice my mistakes ) and then copy and paste. I think that action could make the font size different.

Please give me some minutes and I'll try to explain a real example.

As allways thanks for your help.

[LaRetta]

Oh, I wish I could mini-mice my mistakes as well. :jester:

[Enigma20xx]

It is a list of teachers working on a department.

There are elections every year but I need to keep seeing who has been and when in the department.

When a teacher has an EndDate, I make background red, white and strikeout text. That’s no problem.

But the list is long and I’d like to know if a teacher is twice with a non EndDate.

With LaRetta’s idea I can make what I need, but not completely. I format text as blue, but counts also lines with an EndDate and I don’t want to take them into account.

I have made a quick sample, I hope not to forget anything.

This time I have used paste as plain text. I hope font size is now ok.

Comment I haven't explore very much your way yet.

Many thanks once again.

Ups! sorry. I forgot the sample file.

Deps-LaRettas.zip

[LaRetta]

Your conditional format (on the teacher's Name for the second condition to select blue text, would be:

Count ( Join2::DepartmentID ) - Count ( Join2::EndDate ) > 1

This depends upon DepartmentID never being empty ... another reason that every table should have a unique, auto-enter serial number (to always provide a non-empty field when you need it).

[LaRetta]

What am I thinking? We are not talking about filtering a portal. We are talking about conditional formatting! Of COURSE it is better than adding another table occurrence!!

Please use Comment's method. There is no reason that you can't slide this calc into it - just change counting the end date to same method Michael used in the example.

[comment]

I’d like to know if a teacher is twice with a non EndDate.

See if this helps:

DuplicatesInPortal2.zip

[Enigma20xx]

Once again LaRetta and comment great support.

Thank you very much, fits perfectly my needs.

I made a little change because of my join table.

I upload the file so others can use it. All the knowledge is from LaRetta and comment.

DuplicatesInPortal3.zip

[jkluchnik]

I used to think so, too...

I love your wry, 'minimalistic' statements

Great solution!

LOL. It is amazing how simple it is in concept when shown but how difficult for the mind to come up with to begin with. I am constantly surprised and amazed.

I feel all that, and lets add STUPID, every time I am on this forum!

[jkluchnik]

Comment, as I am always trying to learn...

As I said, your method for duplicates is GREAT! It made me rethink a method I use for checking if something already exists in a value list and I tried it in your duplicates solution. It also worked. I am wondering if there is an advantage of using one method over another, and if so, what is it:

Your solution used the conditional format:

ValueCount ( FilterValues ( List ( Child::Category ) ; Self ) ) > 1

This uses three functions.

I tried using PatternCount instead of ValueCount and FilterValues, and adding the "¶" to the pattern. This then is just two functions.

PatternCount ( List ( Child::Category ) ; Self & "¶" ) > 1

I read a lot about "overhead" and the like and I have no idea if this makes any difference whatsoever. Again, just wondering out loud as I always learn great things from you.

[comment]

Well, to make it fail-safe, you should be looking at =

PatternCount ( ¶ & List ( Child::Category ) & ¶ ; ¶ & Self & ¶ )

Now, it's true it still has one function less, but last time I tested this (a few versions ago) FilterValues() came out much faster than anything else.

[jkluchnik]

As I said, my "one function less" comment was me just trying to look smart. I have no idea if that makes any difference. I'm only trying to learn the different applications of the various functions...

As for the way you wrapped the List and the self in the ¶ at beginning and end, why do you do that? It was my understanding that the results of a list would be in the format Value & ¶. Why add the leading ¶?

[comment]

Because =

PatternCount ( List ( Child::Category ) ; Self & "¶" )

returns 3 when the list contains "Alfred¶Mildred¶Red¶Blue" and Self contains "Red".

[jkluchnik]

You see, that's why they call you..... well, comment

You saved me a potential huge headache in my existing solution. I will now be changing a whole bunch of scripts!

[Andeye]

Sorry for the late question, but...

Is there any way of replicating LaRetta's original solution (that would conditionally format a duplicate StateName even if the original occurrence of that StateName was not in the portal) but using something like Comment's elegant relationshipless solution?

I have a bunch of fields in portals that I would like to conditionally format if they have any global duplicates (same table, different portals).  LaRetta's original solution works, but is going to result in a large number of table occurrences simply to highlight potential duplicates.

PS - I'm new here, should I be able to download the sample files, or is this thread too old?

[comment]

I have a bunch of fields in portals that I would like to conditionally format if they have any global duplicates (same table, different portals). 

​I can't figure out what you mean by that.

 

should I be able to download the sample files

​Please report the issue here:

[Andeye]

Thanks for the rapid response Comment & sorry if I wasn't clear.  

My actual application relates to people:  The table relationships are basically Clients<Projects<Contexts<People.  The People table includes the usual Name, PhoneNumber, Email, LinkedinURL, TwitterHandle, etc. type fields.  People are generally added to the database via a portal in the main Contexts layout which includes a People portal.  I would like to use conditional formatting to highlight if a duplicate Name, PhoneNumber, Email, LinkedinURL, TwitterHandle, etc. was entered.

The difference with Enigma's example is that I would like it to highlight duplicates from anywhere in the database (that's what I meant by global duplicates).

Your solution for Enigma is filtered with the portal, so only highlights duplicates within the same Context.

LaRetta's original solution works as desired, but involves creating an additional table occurrence per field (Name, PhoneNumber, Email, LinkedinURL, TwitterHandle, etc.)

[LaRetta]

I have duplicatesInPortal and duplicatesInPortal2 - both attached.

duplicatesInPortal.zip

[Andeye]

Thanks LaRetta

I think the analogy with DuplicatesInPortal would be that I want to have Bravo & Delta (ChildIDs 3 & 5) in Alpha (ParentID 1) highlighted red too, because they are both also included in Bravo (ParentID 2).  

I'm not sure if that makes my query any more clear...

[comment]

I think the analogy with DuplicatesInPortal would be that I want to have Bravo & Delta (ChildIDs 3 & 5) in Alpha (ParentID 1) highlighted red too, because they are both also included in Bravo (ParentID 2).  

​I still don't understand what you're saying. In the DuplicatesInPortal file, Bravo & Delta (ChildIDs 3 & 5)  are not "included in Bravo (ParentID 2)". 

In any case, if you want to detect duplicates within a table, and you don't want to do it by finding them, then you must have a self-join relationship (either an actual one, or an ad-hoc one, using ExecuteSQL()).

[Andeye]

if you want to detect duplicates within a table, and you don't want to do it by finding them, then you must have a self-join relationship (either an actual one, or an ad-hoc one, using ExecuteSQL()).

When you say 'finding them' do you mean manually finding them, or is this something that can be used for conditional formatting?

I would like to detect duplicates by any reasonable means that can be executed within the conditional formatting dialogue alone (I'd rather not be lumbered with numerous TO's that are simply there to do conditional formatting).

Would the ExecuteSQL() route take excessive processing time?  If not, any hints on the SQL would be very welcome.

I simply want to be able to colour any telephone number, or email address, etc. red in a portal if it has been used anywhere in the People table.

[comment]

When you say 'finding them' do you mean manually finding them, or is this something that can be used for conditional formatting?

​I meant perform a find using the ! find operator. This can be manual or scripted - but in either case cannot be used for conditional formatting.

 

Would the ExecuteSQL() route take excessive processing time? 

​That depends on the amount of records being displayed and the overall amount of records in the table.

 

I'd rather not be lumbered with numerous TO's that are simply there to do conditional formatting

​It's the only way it can work without depending entirely on unstored calculations, each having to go over all the records in the table.

 

Note: you could use a single self-join relationship, using the x relational operator,  to get a list of all the values in any of the table's fields. Then use the same method as the one shown in the DuplicatesInPortal file to count the occurrences of the current value in the corresponding list.

[Andeye]

you could use a single self-join relationship, using the x relational operator,  to get a list of all the values in any of the table's fields. Then use the same method as the one shown in the DuplicatesInPortal file to count the occurrences of the current value in the corresponding list.​

Thanks Comment, one x self-join to find all duplicates is better than the numerous TO's that I thought I'd need.

Should anyone following have the same issue I have uploaded my test file.

DuplicateConditionalFormatting.fmp12.zip

Edited June 17, 2015 by Andeye

[bruceR]

And your realistic record count is going to be .. what?

You are going to use this over LAN? WAN?

[Andeye]

And your realistic record count is going to be .. what?

The legacy system that I hope it will replace is approaching 12,000 records, I could see that doubling in ~2 years given the reduced friction with the FileMaker solution.

You are going to use this over LAN? WAN?

Probably a discussion for another thread, but my current plan is for the users to work on a local copy synced to a cloud server using something like MirrorSync.  The users are very widely spread (Europe, US, Australia, I'm in Mauritius), but to me it is critical that they can get on with their own work at the speed of thought.

[bruceR]

Sounds like you're going to need to move on to another method.

Added a simple duplicate script to get the total up to 12,000 records.

Performance looks pretty slow.

DuplicateConditionalFormattingMOD_BFR.fmp12.zip

[Andeye]

Thanks Bruce

I wasn't surprised that your 12,000 record version was slow, as it was having to run calculations to conditionally format a significant number of records per screen.

I was surprised that even with a layout instance that only had 20 something portal records, performance is still slow.  We are likely to have fewer than 100 People per Context, and probably fewer than 10 Contexts per Project.  I therefore allocated your 12,000 records semi-randomly against 120 contexts but was surprised to find little difference in performance.

Does filemaker really run conditional formatting calculations on values that it doesn't actually have to display?

[comment]

Does filemaker really run conditional formatting calculations on values that it doesn't actually have to display?

No. But you didn't ask about detecting duplicates among the displayed records only. To satisfy your requirement, Filemaker must compare each of the 100 displayed records to the entire list of all 12,000 records in the table - that's 100 x 12,0000 comparisons to perform (for one field).

Edited June 18, 2015 by comment