Sign in to follow this  
Followers 0

Changing colour when duplicate record

21 posts in this topic

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

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

1

Share this post


Link to post
Share on other sites

Posted (edited)

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

See if this helps:

DuplicatesInPortal2.zip

0

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

Because =

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

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

0

Share this post


Link to post
Share on other sites

Posted

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

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

0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0