mikeytt Posted October 28, 2015 Share Posted October 28, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
LaRetta Posted October 28, 2015 Share Posted October 28, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
Lee Smith Posted October 28, 2015 Share Posted October 28, 2015 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 Link to comment Share on other sites More sharing options...
mikeytt Posted October 28, 2015 Author Share Posted October 28, 2015 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. Link to comment Share on other sites More sharing options...
LaRetta Posted October 28, 2015 Share Posted October 28, 2015 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? Link to comment Share on other sites More sharing options...
Lee Smith Posted October 28, 2015 Share Posted October 28, 2015 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? Link to comment Share on other sites More sharing options...
mikeytt Posted October 28, 2015 Author Share Posted October 28, 2015 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! Link to comment Share on other sites More sharing options...
Lee Smith Posted October 28, 2015 Share Posted October 28, 2015 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 Link to comment Share on other sites More sharing options...
LaRetta Posted October 28, 2015 Share Posted October 28, 2015 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? Link to comment Share on other sites More sharing options...
mikeytt Posted October 28, 2015 Author Share Posted October 28, 2015 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 ; "") Link to comment Share on other sites More sharing options...
LaRetta Posted October 28, 2015 Share Posted October 28, 2015 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. Link to comment Share on other sites More sharing options...
mikeytt Posted October 28, 2015 Author Share Posted October 28, 2015 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 Link to comment Share on other sites More sharing options...
LaRetta Posted October 28, 2015 Share Posted October 28, 2015 You already showed us this. Sorry, I will not help you with it. Maybe someone else will. Link to comment Share on other sites More sharing options...
comment Posted October 28, 2015 Share Posted October 28, 2015 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. 1 Link to comment Share on other sites More sharing options...
mikeytt Posted October 29, 2015 Author Share Posted October 29, 2015 Hi comment - absolutely perfect with the Custom Function, exactly what I was after! Link to comment Share on other sites More sharing options...
comment Posted October 29, 2015 Share Posted October 29, 2015 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 . Link to comment Share on other sites More sharing options...
mikeytt Posted October 29, 2015 Author Share Posted October 29, 2015 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. Link to comment Share on other sites More sharing options...
comment Posted October 29, 2015 Share Posted October 29, 2015 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3230 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now