Can you clarify this point, it seems contradictory:
I need to avoid duplicates during the import. Easy enough. But I also need to mark each duplicate record
Are you importing the duplicates or not?
How many tables in the database? How is it structured?
...I know that they generated a new opportunity
Who are 'they'?
Do you have an Employees Table and a Leads Table (leads generated by employees-child table)? If you do, and they are related, then importing correctly into the Leads Table (assuming these are new leads) shouldn't generate any duplicates.
Where/How is the data being imported?
At this point is is still a work in progress, so I can setup whatever I need to. The structure that I have now is:
1. Accounts Table (companies)
2. Contacts Table (people at those companies)
3. Activities Table (activities linked to the Accounts)
Easier to explain with an example. Jim Smith scans his badge at a the 2014 ABC Tradeshow. He is entered into the database. There is a field in Accounts to record the source of the lead, 2014 ABC Tradeshow.
At the 2015 ABC Tradeshow, Jim Smith scans his badge again. He is one of 300 people on a list of leads from the show. When I import that list into the database:
1. I don't want to duplicate Jim Smith.
2. I don't want to update the source field because it will reduce the number of leads that I received from the 2014 show.
3. I don't want to just skip him, because I need a record of how many scans I received from the 2015 show.
My thought (in layman's terms) is to see that Jim Smith is a duplicate, go to a related table called "Activities" and add a new record for 2015 ABC Tradeshow. Over time, I may scan Jim many times at different shows, and I need a record of each.
Entering each lead manually into the system will allow me to do this easily, but I don't want to type in 300-600 leads each time. If I could automate the process that would be ideal. Or if I could get a report of the duplicates, it would be a small enough number that I could process those manually.
Seems like there is some confusion over where to record which data.
I'd set up a table to record each and every scanned data record--only omit duplicates during import for the same badge at the same trade show.
Link these records to your accounts table such that if the same individual scans their badge at two different trade shows, you have one records in accounts, but two records in your table of badge scan data.
How you link the records may be a bit of a problem as names, both of companies and individuals are not unique. These names for companies and individuals can change from time to time.
So you may have to provide some human intervention when linking your scan data to your accounts. Once that is done, any scans that do not link to any existing account data represent new accounts and a script can use the scan data to create new account records linked to these scan records.
The issue with having multiple copies of the same name, even in different tables, is that eventually we want to move the Lead to a Prospect and then to a Customer. With multiple copies, when one is moved, the rest remain and make it confusing down the road when doing marketing activities. There has to be one record that moves through the system.
And that record would be the single record in Accounts. The scan data is just to be able to track how many badges were scanned at a given Event.
And I would not "move" this data. I'd keep it in one duplicate free table and change the value of a status field when it changes from Lead to Prospect to Customer...
Another question, are you scanning the badges right into FileMaker with an iPhone or iPad (either directly or a bar code reader) ? If so, are you creating the badges(barcode/mag strip) yourself or would you need to parse the data. Or is this being done by others and they are sending you the data? If so, how are you receiving it.
Thanks for the input, I think that I have found a solution by simplifying my needs and using FileMaker to my advantage.
I receive an Excel or .csv file with the names from the Trade Show or other source. (Scanning directly to FileMaker is only in my wildest dreams!)
I have decided to keep "leads" in one table, complete with person and company info. This is because it is difficult (for me) to import to multiple tables from one spreadsheet. However, I can export from one table to multiple tables using a script pretty easily.
I added a field called "Lead Status" and of course I have a created date field and a Lead Source field. The lead status of every record in the system was set to "file" but any term would work. I add a "lead status" column to my excel spreadsheet, but leave it blank.
When I import, I use a concatenated field as my matching record field. If there is no match, a new record is created, the Lead Status is blank and the Lead Source is the name of the trade show and the create date is today. If there is a match, the Lead Status of the matched record is updated to blank. After the import, I simply search for records with a blank lead status and the current trade show as the source, then update those records to "file". Then I search again for any records with a blank lead status field. These are records that were duplicates of the records imported. I can then manually add a note to each of these.
The concatenate can be replaced by simply matching a series of fields, depending on if you want to update address information or contact information, which we usually do.
I am writing a script to move a lead to our Accounts database. The reason for this is that our Leads database could have thousands upon thousands of names in it, where our prospects and customers database is much smaller. This will prevent the system from bogging down for the daily users who have to search and filter it often.
I will test it all out and let you know if I come across any issues. It may be kinda kludgey, but I think it will work.
UPDATE: There were some flaws in my logic, but a simple answer presented itself. I created a concatenated field that combines the basic info that I want to use as criteria for a duplicate. I then created a self-join relationship based on this field and a serial number field (found this trick on the forum) Now I can freely import or enter records and easily find duplicates and process them manually.
But you don't describe any duplicates in the data you want to input.
Each time someone's badge is scanned, you get a new record in one table. These are not duplicates because each scan records the fact that a scan was made at a different place and time.
A different related table records you accounts/leads/contacts--whatever you find best to call this data and it links to the scans table. But the relationship is from One company to possibly many badge scans.
I'll call the other table companies here:
Companies::__pkCompanyID = BadgeScans::_fkCompanyID
Note the key difference here: the records are linked by a unique ID from Companies that identifies the company and not a field that identifies the trade show event.
The challenge is to match a record in BadgeScans to a record in Company so that it can be "marked" with the correct company ID value. There may not be any data in your excel file that is 100% reliable as a means of identifying the company as names change and different people can type in the same company name differently--neither are they always unique....
Agreed, no method is going to be exactly foolproof. What I am using is First Name & Last Name & Company Name & City. These 4 fields, concatenated, form a field that I use to determine if something is a duplicate. Spelling differences mean that it won't be perfect, but I have another check later in the workflow that should help.
After an import, one of the duplicate records is marked as "unique" and the rest are marked "duplicate". My database person does a search for duplicate records and makes a case-by-case decision on what to keep and what to eliminate.
When a Lead is moved out of the leads database and into my Accounts database, a search is done for the Last Name only. This will help find alternate spellings of first names and company names. From the similar records, the admin again chooses what to keep and what to get rid of.