You could create a related table for the time stamps, then do a loop through the primary table (events) and store the time stamps in the related table. Then go back to the primary table and write a looping script that removes all duplicate events (well, leave one record of each duplicate - don't erase them all!).
Or you can create an array from an ExecuteSQL such as "SELECT DISTINCT ( EventID ) FROM Events" to get all unique Event number, delete the Events table, and then loop through storing them back into the Events table from the array of distinct EventIDs.
The problem is that import matching does not resolve to 1 record for the event id. All records are imported.
But this should resolve to just one record if:
- The event ID is unique in the target table (the table receiving the imported data)
- The event ID is specified as the match field
- You show all records in the target table before importing. Make sure that the layout where you show all records specifies the same table occurrence that you select as the target table in Import records
#3 is the most common oversight as the import matching process requires that the record to be matched be present in the current found set.
Thank you both.
I am aware of all 3 steps Phil.
I went ahead and made a little db, script and 2 test files but of course it worked perfectly.
I think I have a problem with relationships. ? Maybe. Real life is more complicated that the little test.
If your event ID's are text, be careful that you don't have ID's that look like they should match, but don't actually match due an invisible extra character--the most common would be an extra space or tab character.
Yes, thought of that too. The Ids are numbers.
Attached is my little test, with a script that was modified from the
real thing and yet ....
(did not see how to upload online).
Import events 1, then 2. Presto.
Have you shared an example that works or one where this does not work?
Oh, i have worked so much on this. Recreated things, made my own tests
on and on. Sad.
There is an event id (numeric). Then a date time ID was created using
the event id plus a dash + the number of the occurrence of event.
I have requested a numeric ID, no dash. It is being done.
Later today I may know more.
If it works it it will be incredible.
I made a copy/version of the db that i suppose I could hand over.
How can put the data online? Can't be done.
It does not work correctly.
All I do is import data (in scripts I make) and yet this does not
work. I have never come across a time when importing did not work
I may have to use the solution Taylor suggested.
If you select the "use advanced editor" option, you can attach several files to your reply. This could be a copy of your file plus a file of the test data (please do not use a file with sensitive data in in it for this purpose) if you'd like someone else to examine and test your solution.
The data is sent as Unicode UTF 8.
One of the problems was that there is a field with more that one
value. The values are separated by a carriage return (for example
That worked last year using filemaker 13 and did not work this year
using filemaker 14. This year, the data was all wrong once it hit the
record with multiple values.
Today I scripted read in the file, write out an excel file of the data
and then import the excel file. That problem goes away.
I still do not know why the test file will update matching but not
this data. So odd.
Going to loop throughout the events now.
I do not see 'use advanced editor' option.
DB and xlsx file are sent.
- log in as guest
- the message is about the utf 8 data which is not sent here, doesn't matter
- run script from Menu "Import Registration XLSX"
Why doesn't update matching work for the events db. Yes, all occurs
should be in the date-time db.
You just used it or you would not have been able to attach those files.
My guess, without looking at your files is that your list of values in a single field/cell doesn't match values as expected. Probably because in in one file the values list as:
and the other lists as
So that the same values are listed, but not in the same order.
But return separated lists of values have special features that can affect results. I've never tried using a return separated list of values for matching during import so you might be getting a case where any one value in the imported list of values matches to all records in the target table that list at least that value...
The event file has a numeric event id
the data time file has a numeric date time id
I am not sure that I understand.
I sorted everything before any action is taken (just now) and it is better but not 100%.
Are you referring to the utf-8 data where a field will have more than one value with a carriage return? Why did that work in every instance last year?
That did break up the data this year but it is working now using excel. That field has nothing to do with update matching. It is not an ID field.
The remaining problem is that I do not get one event record per event id.