20 replies to this topic

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

Attached Files

•  DuplicatesInPortal.zip   7.36K   69 downloads

[LaRetta]

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,

Edited by LaRetta, 02 March 2012 - 09:53 AM.

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

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

Attached Files

•  Deps-LaRettas.zip   6.79K   55 downloads

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

Attached Files

•  DuplicatesInPortal2.zip   7.68K   43 downloads

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

Attached Files

•  DuplicatesInPortal3.zip   7.13K   49 downloads

[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".