Exporting X amount of records different from Import

[Christoph]

Hey folks, I have a test file containing 28 "Total" records. I'm exploring FM's Export feature and want to import the data into a duplicate of my test file with all existing records removed. However, upon importing, I found that the total records in the duplicate file amount are 46, not 28. 18 of the 46 records are blank. How do I remove these empty records from the original test file, which I believe are being transferred to the duplicate test file? Thanks.

[comment]

Your description is difficult to understand. Importing adds records to the target file, leaving previously created records intact*. If you imported 28 records and now the target file has 46 records in total, then there were 18 records in the target file before the import.

Note that immediately after importing, the found set will contain only records that were imported*. 

---
(*) This is assuming we are talking about an import using the adding records method, not updating or replacing existing records - see:
https://help.claris.com/en/pro-help/content/importing-data.html

 

2 hours ago, Christoph said:

How do I remove these empty records from the original test file

How else: find them and delete them. Or just omit them before importing - only the found set in the source file will be imported.

Note that you can also use field validation in the target file to prevent records that do meet your criteria from being imported.

You mention export, but I see no actual action of exporting records in process you describe (nor would I expect such action: records can be imported directly from a .fmp12 file).

 

Edited May 24 by comment

[Christoph]

Hi comment, I understand how confusing this may be. I should have created a step-by-step for others, to better understand my problem. I do appreciate the information and link you supplied and I will read through it. If you have a few minutes, I've revised the steps. If you don't have the time, no worries. You've already been extremely generous. Thanks!

I've duplicated a test file on my Mac using the Option and drag method for convenience. Below are the steps I've outlined to better explain the process:

1. Open the original test file and navigate to File > Export Records to export all 28 records. Refer to the included screenshot for guidance.

2. In the "Export Records to File" window that appears, save the file as "Untitled," choose a destination, select the file type as FileMaker Pro, and then click Save.

3. Next, a "Specify Field Order for Export" window will open. Here, move the desired fields from the current layout, then click Export.

4. Now, open the duplicated file and navigate to Records > Delete All Records from the menu. Confirm the action in the popup by clicking "Delete All" when prompted with "Permanently delete ALL 28 record(s)?" At this stage, no records should be visible.

5. Return to the top Menu bar, and go to File > Import Records > File. Locate and select the exported file named "Untitled" and click Open. Choose to "Add source data from selected fields as new records in the target table."

6. In the "Specify Import Order" window, on the left side, it should display 1 of 46 records. However, since only 28 records were exported, this number should be 28. Refer to the provided screenshot. Click Import at the bottom right. Afterward, an "Import Summary" window will appear indicating that 46 records were added. Click OK, but note that there are 18 records with missing information, which is unexpected.

This discrepancy is confusing as to why the imported record count does not match the expected 28.

Thank you.

[Søren Dyhr]

My initial thoughts when reading this is, why export at all, since imports in an extra defined table inside the "app" could be assigned as destination for imports internally, which even respects found sets of records???

I use it all the time with orders of say 4 partout tickets, which then should split out in individually ID'ed ticket numbers. I'm using the option to import from repeating calculation fields into individual records.

This prevents having all kinds of files floating around, in need for some housekeeping!

Just to say that I never have experienced received blanks stacking up.

--sd

[comment]

I still don't follow the plot. You say you exported records to a file named Untitled. Then deleted all records in this file. And now (in step 5), while you're still in this file, you want to import records from a file named Untitled? This cannot work because (a) there are no records in this file, and (b) this file has only one table and you cannot import a table to itself.

Is it possible you have another file named Untitled and you are importing from there?

 

[Søren Dyhr]

1 hour ago, comment said:

Is it possible you have another file named Untitled and you are importing from there?

That might just be the case or some of the fields are defined as repeaters and the option to split into individuals is selected! Some of the repeaters could have Char ( 9472 ) or similar in the aparently "unused" repetitions?

Being that void and "" isn't the same...

--sd

Edited May 24 by Søren Dyhr

[Christoph]

Hey folks, my initial intention was simply to duplicate an FM file, keeping all records intact in one (let's label it file 1) and completely clearing the records in the other (file 2). Then, I aimed to export the records from file 1 and import them into file 2. Upon doing so, I am left with several blank records which should not be there. It's possible that the file I'm working with is corrupted or contains remnants of unused tables, fields, values, etc., that should have been removed. I might already know the answer, but does FileMaker offer a feature that informs users about unused tables, fields, and other elements, so they may easily be removed? Additionally, I haven't imported from another file named "Untitled."

I must admit that much of the discussion between you and Soren is slightly beyond my current understanding of FileMaker. Nevertheless, I am grateful to both of you for taking the time to explain these concepts to a novice like myself.

Thanks again!

[comment]

2 hours ago, Christoph said:

Upon doing so, I am left with several blank records which should not be there

All I can say at this point is that the problem cannot be reproduced using your description. And I am not going to entertain wild speculations regarding what might be missing from your description. There's probably a very simple explanation right under your nose (https://en.wikipedia.org/wiki/Occam's_razor).

I am also not sure what is the point of this elaborate exercise. If you want to make a copy of your file without any records, you can simply save it as a clone (no records). That's one step instead of your steps 1 to 4. And records can be imported directly from the source file, without exporting first.

Note also that when importing records manually, you can see in the left pane of the Specify Import Order dialog how many records are in the source table and you can step through them and examine the data in each field of every record. 

 

2 hours ago, Christoph said:

does FileMaker offer a feature that informs users about unused tables, fields, and other elements, so they may easily be removed?

Filemaker offers an extensive report on the file's structure in the form of Database Design Report: https://help.claris.com/en/pro-help/content/documenting-schemas.html. And there are 3rd party tools designed to help with the analysis of a DDR. But Filemaker does not keep track of usage and cannot tell you which elements are unused. 

 

[Christoph]

My colleague and I embarked on this exercise because we're each designing distinct user interfaces utilizing the same underlying tables and fields. It started as a straightforward test. However, upon sharing the exported file with her, she noticed numerous blank records. Perhaps I made more fuss about it than necessary. While I realize that my description might not be sufficient to reproduce the issue, I do acknowledge that it likely has a straightforward solution. I've been meticulously inspecting each record's data in search of the root cause.

Thank you for providing the link to the file's structure in the Database Design Report format. It's incredibly helpful, and I'll also explore third-party tools, which I'm sure will come in handy.

Once again, comment, I appreciate your assistance and the time you've dedicated to this matter. Thanks!

[Søren Dyhr]

26 minutes ago, Christoph said:

My colleague and I embarked on this exercise because we're each designing distinct user interfaces utilizing the same underlying tables and fields.

There is then absolutely no need for export, cloning would suffice ... and from there, study how to implement the separation model ... by directing all the relational stuff in the clone, to the original file!

Take a look here at Todds Geists video:

--sd

[Christoph]

Thank you, Søren, for also sharing that valuable information. These kinds of videos will certainly elevate my experience with FileMaker.