What happens if (try on backup first) you drag the sheet onto the FM DB icon?
Hard to tell without seeing the sheet, but what I did for my situation, was to import the sheet to a separate table, then parse the data into the correct table. In another instance, I had the ID fields on the spreadsheet, making the import and matching the fields much easier
If you have your vendor table defined with a vendor name and a vendor ID that auto-enters either a serial number or Get ( UUID ),
You can set up a "unique values" validate always validation option on your vendor name field. Then import from the excel sheet into vendor. The duplicates will be automatically filtered out during import. But be sure to enable auto-enter options during the import so that each record created by the import will auto-enter a unique ID.
Then import from the same sheet into your transactions table. When mapping fields, be sure to map the vendor name column to a vendor name field in Transactions.
Use a temporary relationship matching records by vendor name to copy over the Vendor ID into the Vendor ID field in Transactions. This can be an auto-enter field option with auto-enter options enabled during the import or you can use replace field contents immediately after the import to copy over the VendorID from the vendor records.
Once you have copied over the ID, you can switch to a relationship based on Vendor ID.
Not only do I find FM a challenge but I don't see a reply button on your post to me so I don't know if you will see this.
Thanks for your suggestion, it makes perfect sense but I am stumbling still. When I import into a new table field with unique value validation selected I get the full list with blanks and repetitions. A bit frustrating.
Post a new Answer is misnamed. It is the correct way to reply back to the comments of others.
You'll need to describe your data and on exactly which field in that table you specified "unique values", "validate always" before importing the data.
Thanks for the forum tip. For this test I created a db with one table and one text field with validation set for unique value and user cannot override. I then imported a single name field from my bento address book. What I hoped to see is a list of the unique names in the address book. What I got was a list of records matching the original address book table.
This is puzzling. Also I don't see how to do the same thing with an exiting table already in the filemaker db.
You'll need to specify "validate always" for your field with the Unique Values validation option, I believe.
Also I don't see how to do the same thing with an exiting table already in the filemaker db.
And what exactly do you mean by that? You can always open Manage | Database and change the validation field options on a field before importing more data into that table.
Thank you, validate always was the missing item. I can now import tables of unique records and give them a unique record ID.
I am getting my head around the fact that queries, forms, and reports are all bundled as layouts which is a bit confusing to start with.
Do you know if I can design a query (layout) and make the results a table?
They are NOT bundled as layouts. The crucial detail that establishes "context" for your layouts, scripts, calculations, found sets, imports, exports and so on is the specified Table Occurrence--a "box" from Manage | Database | Relationships. Each layout specifies a particular table occurrence as its context in the "show records from" drop down that is found both in the new layout wizard and in Layout Setup...
For more on table occurrences,see: Tutorial: What are Table Occurrences?
After a fair amount of head scratching, reading and playing I think I have a light bulb above my head. Table occurances seem to be the missing queries I am looking for! In addition populating the tables has become a bit easier with a few importing tricks. It could be clearer that one can import from another fm file. Anyway thanks it was really helpful to kick start what I have been reluctant to attempt for a long time.
It can be useful to people new to FileMaker but not new to SQL queries to think of Table Occurrences as "Select *" queries that have all the needed "join" clauses--the links to other related tables, but no WHERE or ORDERBY clauses--as those are supplied dynamically via performing a find or sorting records from menu picks or scripts.