Jump to content

  •  

Photo

Changing colour when duplicate record


  • Please log in to reply
20 replies to this topic

#1 Enigma20xx  apprentice

Enigma20xx
  • Members
  • 110 posts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 4d 23h 47m 37s

Posted 02 March 2012 - 07:54 AM

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.
  • 0

#2 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 March 2012 - 08:59 AM

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#3 Enigma20xx  apprentice

Enigma20xx
  • Members
  • 110 posts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 4d 23h 47m 37s

Posted 02 March 2012 - 09:22 AM

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.
  • 0

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 March 2012 - 09:30 AM

Aha, you were filtering the portal or the original relationship. Makes sense. I'll know to verify that possibility in future.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 1h 49s

Posted 02 March 2012 - 09:43 AM

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


I used to think so, too...

Attached Files


  • 1

#6 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 March 2012 - 09:50 AM

: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 by LaRetta, 02 March 2012 - 09:53 AM.

  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#7 Enigma20xx  apprentice

Enigma20xx
  • Members
  • 110 posts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 4d 23h 47m 37s

Posted 02 March 2012 - 10:05 AM

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.
  • 0

#8 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 1h 49s

Posted 02 March 2012 - 10:08 AM

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?
  • 0

#9 Enigma20xx  apprentice

Enigma20xx
  • Members
  • 110 posts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 4d 23h 47m 37s

Posted 02 March 2012 - 10:22 AM

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 :laugh: ) 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.
  • 0

#10 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 March 2012 - 11:04 AM

Oh, I wish I could mini-mice my mistakes as well. :jester:
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#11 Enigma20xx  apprentice

Enigma20xx
  • Members
  • 110 posts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 4d 23h 47m 37s

Posted 02 March 2012 - 11:20 AM

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.

Attached Files


  • 0

#12 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 March 2012 - 02:08 PM

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).
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#13 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 March 2012 - 02:14 PM

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#14 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 1h 49s

Posted 02 March 2012 - 04:32 PM

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


See if this helps:

Attached Files


  • 0

#15 Enigma20xx  apprentice

Enigma20xx
  • Members
  • 110 posts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 4d 23h 47m 37s

Posted 03 March 2012 - 11:43 AM

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.

Attached Files


  • 0

#16 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 03 April 2012 - 08:03 AM

I used to think so, too...


I love your wry, 'minimalistic' statements :)
Great solution!

: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.


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

#17 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 03 April 2012 - 08:20 AM

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.
  • 0

#18 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 1h 49s

Posted 03 April 2012 - 08:35 AM

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.
  • 0

#19 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 03 April 2012 - 08:41 AM

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 ¶?
  • 0

#20 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 1h 49s

Posted 03 April 2012 - 08:52 AM

Because =

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

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




FMForum Advertisers