RemoveDuplicate extended

[mikeytt]

I've been trying to adjust a custom function "removeduplicates" from the brian dunning site to do the following....

I have a calculated field which returns a list collated from a relationship in the form of a block of text.

It has 3 items per line - date, time and subject - each element separated by a tab, and each line with a carriage return

10/24/2015     06:00    Alpha

10/24/2015     07:00    Beta

10/24/2015     07:00    Charlie

10/31/2015     06:50    Delta

10/31/2015     07:30    Echo

What I want returned is...

10/24/2015     06:00    Alpha

                       07:00    Beta

                       07:00    Charlie

10/31/2015     06:50    Delta

                       07:30    Echo

So basically removing the duplicate dates from the list.

The RemoveDuplicate function on brian dunning was designed for a list with single words per line and try as I might I can't get it to look at just the first word on each line and remove the duplicate dates without messing up the formatting (the tabs and carriage returns) of the text. Any help would be eternally appreciated!

 

RemoveDuplicates (Text)

Let ( [

TheValue = LeftValues ( Text ; 1 ) ;
TheCount = ValueCount ( Text ) ;
NewText = Replace ( Substitute ( "¶" & Text & "¶" ; [ "¶" ; "¶|" ] ; [ "|" & TheValue ; "" ] ; [ "|" ; "" ] ) ; 1 ; 1 ; "" )

] ;

     Case ( TheCount > 0 ;

          TheValue & RemoveDuplicates ( Replace ( NewText ; Length ( NewText ) ; 1 ; "" ) )

     )

)

http://www.briandunning.com/cf/492

Edited October 28, 2015 by Lee Smith
added the link

[LaRetta]

I have a calculated field which returns a list collated from a relationship in the form of a block of text.

Hi Mikey,

Why not simply produce a report from that related table?  You can add a leading part based upon the date, put the date in it.  Then in the body, put the other fields.  Sort by date.  Done. :-)

If you can't do that, the next best thing would be to use a portal to that relationship.  Sort it by date.  Then attach Hide (or conditional formatting if you are truly still in version 11) which would hide the date on subsequent lines.  It might look like this (untested):  relatedTable::dateField = GetNthRecord ( relatedTable::dateField ; Get ( RecordNumber ) - 1 ) 

Otherwise you'll need to explain the purpose of your calculation, which would be terribly inefficient and resource-wasteful.  You certainly aren't using it for export because export usually needs all data on each line.  Truly, gathering all related records into a calculation in a parent table is very costly particularly when the data is available natively in portal!

Edited October 28, 2015 by LaRetta

[Lee Smith]

Why did you post this here instead of the regular Custom Function Topic? This looks like a report need, not a CF IMO.

Here is a quick example I made to show what I mean.

 

mikeytt.fp7.zip

[mikeytt]

Thank you for your responses - This is for a report but that block of text I'm trying to clean up is combined with many other calculated blocks of text in a single calculation which also styles the text.

The single big calculation collating all the text appears the only way to achieve the layout and design required for the report, hence the requirement to be able to clean up that particular text block using a custom function.

[LaRetta]

I strongly suggest that you rethink that "block of text I'm trying to clean up is combined with many other calculated blocks of text in a single calculation which also styles the text."

Calculations which reference other calculations which ... it is a bottomless pit which should not exist.  The solution is to clean it up.  We will help you do so if you are willing.  Do not compound your problem now by adding yet another unnecessarily-complex calculation.  Can you post your file?  If not, can you post a sample file showing just the fields used in this convolution so we can show you how to get yourself out of this trap?

[Lee Smith]

 Wow.

I agree with LaRetta.

This is for a report but that block of text I'm trying to clean up is combined with many other calculated blocks of text in a single calculation which also styles the text.

Lets start by verifying if your are reflecting the correct information in your profile. i.e. Have you upgraded your version of FileMaker?

[mikeytt]

Sorry, on V14 so updated my profile now.

Unfortunately I can't upload the DB - it's one that my brothers' ex-partner did for him and he just needs this specific change. Ideally I'd redo the whole thing from scratch, when I have the time, and will be able to avoid the above problems you have mentioned.

IN the meantime though I need to find a solution that will fit into the existing way the DB has been structured, so in this case the Custom Function to clean that block of text. Also the inquisitive side of me now wants to know if it is actually possible to not to do it via a custom function, even if it isn't necessarily the best way to do it!

[Lee Smith]

Maybe you could create a mock up of the file? or Save a no-record clone of it and a few example of the data?

if you decide to go this route, I want you to start a new topic in the Relationship Topic http://fmforums.com/forum/54-relational-database-theory/

Lee

p.s. I think this is the first time I have suggested that someone start a new topic 

[LaRetta]

But here's the thing ... you obviously have a relationship to that related table where the data exists.  Why not at least use a portal to pull only the data you need, instead of pulling it ALL and then having to strip it apart again.  How is this calculation being used?  Is it only displayed on the layout?

[mikeytt]

It's only being displayed on a layout for print (screenshot attached) but the fields showing are only shown if relevant which is why I think it was set up the way it was. Here's the calculation which pulls it all together (there's other ones as well for other printed pages which all run on from each other):

 

 

TextSize (
TextStyleAdd ( "Event Concept"  & "¶" &

TextColor (
"_____________________________________________________________________________________________"
;RGB (200;200;200))

; Bold )
; 10 )

& "¶¶" &

TextStyleRemove ( Event_Brief ; Bold ) &

//If ( not IsEmpty(Event_Brief)    ;     "¶¶" &    TextStyleRemove ( Event_Brief ; Bold )  ; "")

//EVENT DETAILS

"¶¶¶" &

TextSize (
TextStyleAdd ( "Event Details: "& "¶" &

TextColor (
"_____________________________________________________________________________________________"
;RGB (200;200;200))

; Bold )
; 10 )

& "¶¶" &

"Event title:" & "    " & Events::event_title & "¶" &
"Event type:" & "    " & Event_Style & "¶" &
"Start date:" & "    " & Event_Date & "¶" &
"End date:" & "    " & Event_Date_End & "¶" &
"Start time:" & "    " & guest_arrival & "¶" &
"End time:" & "    " & guest_departure & "¶" &
"Guests:" & "    " & event_numbers & "¶" &
"Event Status:" & "    " & Event_Status

 

//EVENT TIMINGS

& "¶¶¶" &

TextSize (
TextStyleAdd ( "Event Timings: "  & Quotes::Event_Date & "¶" &

TextColor (
"_____________________________________________________________________________________________"
;RGB (200;200;200))

; Bold )
; 10 )

& "¶¶" &

combinetimings

 

//VENUE DETAILS

& "¶¶¶" &

TextSize (
TextStyleAdd ( "Venue Details: "& "¶" &

TextColor (
"_____________________________________________________________________________________________"
;RGB (200;200;200))

; Bold )
; 10 )

& "¶¶" &

If ( not IsEmpty (Venue_Contacts_Primary::full_name)  ;  "Contact name:" & "    " & Venue_Contacts_Primary::full_name & "¶" ; "") &
If ( not IsEmpty (Venues::company)  ;  "Venue name:" & "    " & Venues::company & "¶" ; "") &
If ( not IsEmpty (Venues::add1)  ;  "Address 1:" & "    " & Venues::add1 & "¶" ; "") &
If ( not IsEmpty (Venues::add2)  ;  "Address 2:" & "    " & Venues::add2 & "¶" ; "") &
If ( not IsEmpty (Venues::city)  ;  "City:" & "    " & Venues::city & "¶" ; "") &
If ( not IsEmpty (Venues::country)  ;  "Country:" & "    " & Venues::country & "¶" ; "") &
If ( not IsEmpty (Venues::zip)  ;  "Postcode:" & "    " & Venues::zip & "¶" ; "") &
If ( not IsEmpty (Venues::tel_1)  ;  "Telephone:" & "    " & Venues::tel_1 & "¶" ; "") &
If ( not IsEmpty (Venues::tel_mobile)  ; "Mobile:" & "    " & Venues::tel_mobile & "¶"  ; "") &
If ( not IsEmpty (Venues::email)  ;  "E-mail:" & "    " & Venues::email ; "")

 

[LaRetta]

Good grief.  

Again, I ask, why not simply use a portal for your new need.  I'll skip further comments on the existing 'report' calculation.

You show a calculation within that calculation of "combineTimings".  That is what you want repurposed, correct?  What is the name of the table where that calculation gets its data?  

Really, you can do what that 'combineTimings' calculation is doing but you need to help us by providing the relationship and how the tables are related (just this part - not your whole schema).  You do NOT want to take the above calculation and pull out what you need, apply a custom function to strip partial dates and create a calculation out of THAT result - not when it can simply be gathered directly from the relationship.

 

[mikeytt]

I probably will use a portal instead.

However my desire now is to know if a custom function could do this - so, forget for a minute that the text being worked on has come from a related field or where it's going to be displayed.

Let's say it was just a block of text that was copied into a field from a word document - how could a custom function clean it up like I require?:

So from this...

10/24/2015     06:00    Alpha

10/24/2015     07:00    Beta

10/24/2015     07:00    Charlie

10/31/2015     06:50    Delta

10/31/2015     07:30    Echo

to this...

10/24/2015     06:00    Alpha

                       07:00    Beta

                       07:00    Charlie

10/31/2015     06:50    Delta

                       07:30    Echo

[LaRetta]

You already showed us this.  Sorry, I will not help you with it.  Maybe someone else will.

[comment]

 

The best way to get a printout like that would be to use a layout of a table where each timing item is a record (this would be a child table of Events). These items would be in the body part, and everything else would be in either sub-summary or grand summary parts. To group these items by date, you would use either a a sub-summary part or conditional formatting to show only the first date in each group.

Keep in mind that Filemaker is a database - i.e. a tool for storing and processing structured data, divided neatly into records and fields. It is ill-equipped to handle unparsed text stored in a single field.

That said, the following custom function will do what you ask for, provided the values are already sorted:

IndentGroups ( text ) =

Let ( [
n = ValueCount ( text ) ;
currValue = GetValue ( text ; n ) ;
prevValue = GetValue ( text ; n - 1 ) ;
currGroup = LeftWords ( currValue ; 1 ) ;
prevGroup = LeftWords ( prevValue ; 1 ) 
] ;
Case ( n > 1 ; IndentGroups ( LeftValues ( text ; n - 1 ) ) & ¶ )
& 
Case ( currGroup ≠ prevGroup ; currGroup ) & Char ( 9 ) & RightWords ( currValue ; WordCount ( currValue ) - 1 )
)

Note that this assumes that the group names (the values in the first "column" of the "table") do not contain spaces or other word-delimiting characters.

[mikeytt]

Hi comment - absolutely perfect with the Custom Function, exactly what I was after!

[comment]

absolutely perfect with the Custom Function

No, it is not. I strongly suggest you pay attention to what both LaRetta and I suggested: produce the report from the child table. And use layout objects and formatting instead of calculating the entire text .

[mikeytt]

When I get a chance to re-do this database I will be using layout objects etc - this was a case of trying to find a solution to fit the existing structure of the database to make a quick fix.

More importantly to me, I really wanted to know how to form a Custom Function to be able to remove duplicate items from a text structure in this format. I certainly better understand that now and I can see also now how I can adapt this Custom Function for similar cleaning of text blocks (text copied to fields rather than calculations built from relationships and tables). This is primarily where the "absolutely perfect" comment was targeted at, sorry if that wasn't clear, but a big big thank you nevertheless.

[comment]

This is primarily where the "absolutely perfect" comment was targeted at, sorry if that wasn't clear,

Oh no, you were perfectly clear. I just wanted to stress the point of not using this other than as a stop-gap until you put it right.