MightyData

FileMaker and WordPress Consultants

  • Company
    • About
    • Contact
    • Team
    • Artists
  • Services
    • Consulting
    • Development
    • Our Process
    • Coaching
    • Training
  • Results
    • Customers
    • Success Stories
    • Testimonials
  • Blog

Dealing with Duplicate Records

October 17, 2013 by Anders Monsen 2 Comments

Looping script to flag duplicates

A common issue in all databases is dealing with duplicate records. In FileMaker I tend to use two different methods to identify duplicate records. Each serve their own purpose. One method uses a self-join relationship with a calculation that marks the duplicate records, while the other method uses a script that sort and loops through records.

Three young girls using a laptop

On the surface, both methods are fairly simple, such that when memorized will come to your aid time and time again. If you maintain a database template or a technique file, you can set up both methods and refer to these as needed.

Self-Join

The first method, removing duplicates via a self-join, is drawn directly from FileMaker’s Help manual. It is a technique that has existed for several years, simple and elegant. This method requires two additional fields in your table (or three, if you do not yet have a field with your “unique criteria”). Once you determine which field makes your record unique (which may require a calculation; the example uses name + phone number), you create a self-join relationship. You do this by creating a new table occurrence. I usually call this “MyTableName__Self” (with MyTableName switched to the actual table with duplicate records, of course). Make the relationship key the UniqueField = UniqueField.

With the self join in place, create your two new fields, the Counter and the DuplicateFlag (or whatever you want to call them). Set aside the counter field for now. Make your DuplicateFlag field a calculation. This is a simple IF statement:

If( MyTable::Counter = MyTable__Self::Counter ; "Unique" ; "Duplicate" )

Make the Counter “Auto-Enter Serial”. Then, exit the Manage Database window, and in your layout select the Counter field. With all fields showing, add a serial key by clicking in the field and selecting Records>>Replace Field Contents from your menu. Select Replace with Serial Numbers. All fields should populate, and the DuplicateFlag immediately will update. You then can search for any duplicates. Records update live, so to find new duplicates just run a search.

Looping

The second method loops through the found set and checks each record against the previous record. There are more steps involved, but the benefit is that you control what you want to check. This is useful for reports or displaying certain data to a user by finding or omitting records as needed. This method also does not need any schema changes, like the self-join.

Looping script to flag duplicates

Your script will sort the records based on the unique identifier, in this case a phone number. You set a variable to the phone number, then go to the next records in a loop. For each record, you compare the variable to the current record’s phone number. If they match, you set your flag and/or omit the record. If they don’t match, you update the variable and go to the next record. The loop continues until all records in the found set are checked. In this example, I only showed the duplicate record(s) to make sure the flag works. Usually you keep the duplicate omitted.

Conclusion

Both these methods are quick and simple techniques. There is nothing fancy involved, although the concept of “self-join” may sound obscure to beginners. Either will work to clean up duplicate data in a flash.

Filed Under: Scripting Tagged With: Data conversion

The GoZync 4 Migration Experience

September 26, 2013 by Anders Monsen Leave a Comment

ZyncMigration

I have developed a few FileMaker Go solutions which use SeedCode’s GoZync tool to sync mobile and server data. One mobile solution required a dozen tables, which GoZync 3 handled fairly well, but took a few minutes to sync. When SeedCode released GoZync 4, they boasted a significant speed increase. Naturally, that made this particular solution a prime candidate to update. Aside from speed changes, GoZync 4 also eliminated dedicated syncing layouts on the mobile and hosted files, moving these to the GoZyncMobile file instead. This made for cleaner customer files. However, migrating a solution built around the architecture of v3 to a brand new architecture seemed daunting.

The SeedCode crew informed me that migration was not that complicated, and an hour or less might be all that was needed to migrate from v3 to v4. While that might be case for smaller mobile files, reconfiguring my mobile file for a dozen or so tables took a little longer. Overall, the process went smoothly, as advertised. Rather than a step by step walk through of migration, which SeedCode already has created, here are some thoughts on the process.

Having a backup of all files is crucial. Don’t just back up the mobile file, but also your hosted file (or mothership files, as SeedCode calls them). Once you’ve completed your migration, you can clean out your old syncing layouts in both files. With backups you can revert to good copies if anything goes wrong.

Migration is almost like a brand new integration. With GoZync you deal with at least five (5) files:

  • GoZyncHosted (GZH)
  • GoZyncLicence
  • GoZyncMobile (GZM)
  • Your hosted file
  • Your mobile file

However, all connections to the mobile and hosted files are made in GoZyncMobile, and integration then takes place in GoZyncHosted as before. In GZM, setting up the connections in the Relationships Graphs is simple. With the GoZync TO in the middle, you add hosted files on one side, and mobile files on the other. (See diagram 1.)

Relationships to mobile and hosted files

Diagram 1: Relationships to mobile and hosted files

Next, you create your syncing layouts. Follow SeedCode’s format and group your syncing layouts in mobile and hosted folders. (See Diagram 2.) Then you can bring up multiple windows side by side and check that the fields are present for syncing. No field, no data sync. Caveat: calculation fields are tricky, but visually I didn’t get feedback about a calculation field on the layout, only when syncing. This might be due to the ability now to sync a calculation field to a non-calculation field, which is a nice bonus.

Take advantage of hosted and mobile layout folders

Diagram 2: Layout folders for mobile and hosted layouts

Once your mobile file is set up, the next item is to configure your hosted file. The interface has changed since  v3. You still sync each table, but the process makes it easy to get an overview of all tables being synced. You also can set certain tables to either push, pull, or both. (See Diagram 3.) Some of my tables were for reference only, and so I ended up pulling down half the tables, and both pushing and pulling the other half.

Diagram 3: Interface to sync each table

Diagram 3: Interface to sync each table

Then, it’s just a matter of replacing the scripts in your mobile file with the new scripts from the sample mobile file, hooking up the buttons, and syncing to test. I found that GoZync 4 is significantly faster than v3.

Filed Under: Scripting Tagged With: Data conversion, FileMaker Go, GoZync

Using SQL to Duplicate a Record Set

July 26, 2013 by Anders Monsen Leave a Comment

Duplicating a single record in FileMaker is simple. However, what if you are faced with duplicating not just a record, but all related child records? I wanted to accomplish this without modifying the graph, and without looping through records to build my data sets.

For my customer’s development project, I had a table of tickets with associated time, material, and other related records where sometimes only minor changes to a big ticket were needed. Rather than create the related records from scratch each time, we needed a fast and simple method to duplicate key elements from each related table, and at the same time retaining the relation information from the newly created ticket or parent table.

I chose to use FileMaker Pro 12’s native SQL to build arrays with field/value pairs. Unlike a language like PHP, which has a built in logic for handling arrays, in FileMaker these have to be constructed using certain text-character separators, and therefore are not iron-clad arrays. However, once I constructed the, um, pseudo-arrays I could loop through the array values and set my new records.

Although it’s quite possible to set up an interface where you list all fields in related tables that need to be duplicated, I chose to set my field names within the SQL statement. Any changes to fields then need to be made inside the SQL statement, although with a little more programming my solution could conceivably adopt the field storage option. For the sake of this example, I elected to keep the syntax in the SQL statement fairly simple (no custom functions, although the GetFieldName function accounts for any Table Occurences name changes). If field names change or you want to add more fields, the SQL needs to be updated. Also, in regards to your own fields, you need to be aware of any reserved names which will cause the SQL to fail.

Download example file Tickets.fmp12.

The Related Table Query

To begin with, I set a variable with the original ticket number so I could message the user after the script finished. Then I used SQL to select the fields for each related table, and set my array. The example from the time table shows this format. I concatenated the field name and the value from the field. For number, date, and time fields I found that I needed to use the double pipe concatenation symbol instead of the plus sign; using the plus sign for these non-text field types caused some strange behaviors, usually stripping out the value.

In order to later parse out the values from my pseudo-array I added “^” as the field separator. I picked this character I didn’t anticipate it to exist in any field, unlike commas. You may notice the row separator uses a tilde or ~ symbol, instead of a pilcrow or return symbol which is the default separator. The reason for this is explained below in the script parameter section for the subscript the creates the new records.

Let([
query = "
SELECT
'" & GetFieldName ( TIME_ENTRY::NameFirst ) & "|'+namefirst,
'" & GetFieldName ( TIME_ENTRY::NameLast ) & "|'+namelast,
'" & GetFieldName ( TIME_ENTRY::TimeBegin ) & "|'||timebegin,
'" & GetFieldName ( TIME_ENTRY::TimeEnd ) & "|'||timeend
FROM time_entry
WHERE id_ticket = ?";
result = ExecuteSQL( query ; "^" ; "~" ; TICKET::ID )
];
result
)

With my result sets in hand, I duplicated the ticket with the “Duplicate Record/Request” script step. Since I used Get (UUID ) for my primary table ID I had to uncheck the “Do not replace existing value of field (if any)” check box, otherwise the UUID would also get duplicated.

Alternate Duplicate Record Option

If you do not use the “Duplicate Record/Request” you can perform the same action using New Record/Request. First, you would need another SQL statement, grabbing only select items from the record to be duplicated.

Let([
query = "
SELECT
'" & GetFieldName ( TICKET::TicketDescription ) & "|'+ticketdescription,
'" & GetFieldName ( TICKET::TicketName ) & "|'+ticketname,
'" & GetFieldName ( TICKET::ID_Customer ) & "|'+id_customer
FROM ticket
WHERE id = ?";

result = ExecuteSQL( query ; "¶" ; "" ; TICKET::ID )
];
result
)

In order to set the fields you loop through the SQL result, adding pipe separators between the fields (or another separator that later can be switched). With my field/value pair I could use the “Set Field by Name” script step.

Set Variable [$itemCount; Value: ValueCount( $ticketResult )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ticketResult ; $i )]
  Set Variable [$field; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 1 ) ]
  Set Variable [$row; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 2 )]
  Set Field By Name[ $field; $value]
End Loop
Commit Records/Requests []

After Parent Record duplicated

Next, I set variables with my new ID and ticket number. The new ID would be used in the subsequent related records. I called a subscript for each related table, passing in the ticket ID, the layout of the related table, and the SQL result for that related table’s values, which is spelled out in the example file. In the subscript I used a return delimited set of values for the script parameters. To address the point raised above with the tilde separator, if I had used the default return delimiter between rows in my ExecuteSQL my script parameter method would strip out everything but the first row or record. To prevent this I used the tilde, and then in the subscript added back the pilcrow/return character as the row separator. To validate the values in my Data Viewer as I tested the scripts I have this in two actions.

Set Variable [$SQL_Lines; Value: GetValue( Get( ScriptParameter ); 3)]
Set Variable [$SQL_Result; Value: Substitute( $SQL_Lines ; "~" ; "¶" )]

If you use a different method of passing multiple parameters, you just adapt the SQL and subscript to handle this. My goal with the example file is to keep it simple with no custom functions.

The subscript, “Duplicate_RelatedTable”, goes to the relevant layout, then uses a nested loop to create the record and set the values. Since the ticket’s time entry portal might contain multiple values, I needed to create a new record for each one, and set the relevant values from the previous parent record. Since I used the same Foreign key naming convention this let me use Set Field by Name for the Ticket ID in the related tables.

Go to Layout [$Layout]
Set Variable [$itemCount; Value: ValueCount( $SQL_Result )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ SQL_Result; $i )]
  New Record/Request
  Set Variable [$ForeignID; Get ( LayoutTableName ) & "::ID_Ticket" ]
  Set Field By Name[$ForeignID; $TicketID // from script parameter ]
  Set Variable[ $record; Value: Substitute( $row ; "^" ; "¶" ) ]
  Loop
    Exit Loop If [Let ( $j = $j + 1 ; If ( $j > ValueCount( $record ) ; Let ( $j = "" ; True ) ) )]
    Set Variable[ $data; GetValue( $record ; $j ) ]
    Set Variable [$field; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 1 ) ]
    Set Variable [$row; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 2 )]
    Set Field By Name[$field; $value]
  End Loop
Commit Records/Requests []
End Loop

The counter is handled within the Exit Loop condition. As there are multiple loops I wanted to make sure I cleared out all the values of the counter variable after each section within the loop completed.

With all this in place, the user only needs to click on the “duplicate record” button, and the script runs. Once done they see a message as to which ticket number was duplicated and the new ticket number. Success!

Filed Under: Calculations Tagged With: Data conversion, Demo file, ExecuteSQL, FileMaker 12

Let’s get started on your project.

It will be more fun than you think.

Get in Touch

  • Company
  • Services
  • Results
  • Blog

Copyright © 2023 · Parallax Pro Theme on Genesis Framework · WordPress · Log in