I am an archaeologist building a FileMaker database for mobile data collection (via FileMaker Go) and am having some trouble exporting data in the correct format for our backend database, which is in PostgreSQL. The backend database has equivalents for some, but not all of the FileMaker fields, as well as additional fields which are only in the PostgreSQL database. I need to export data from FileMaker into a CSV file which can then be imported into PostgreSQL.
My question is: how do I best format my CSV output so that it is clear which destination fields in PostgreSQL each FileMaker field should import into? Should I provide a diagram (or schema crosswalk) showing how they map and then simply provide a CSV with only the fields which have a destination field in PostgreSQL, along with blank fields so that the CSV file matches the field order in the destination table?
Related question: How do I include blank fields in the CSV where no source fields exist? FileMaker's "Export records" script step only lets me adjust the ordering of the source fields, not add empty placeholder fields to match the ordering and fields present in PostgreSQL destination tables. I've also tried building layouts which only display the equivalent fields to help automate the export, but have run into issues there with not being able to add blank fields without affecting the structure of the source table.
|FileMaker Source Table (Survey Project)||Maps To||PostgreSQL Destination Table (project)|
|Transect Width||NO EQUIVALENT FIELD|
|Ground Visibility||NO EQUIVALENT FIELD|
|Environment||NO EQUIVALENT FIELD|
|Vegetation||NO EQUIVALENT FIELD|
|NO EQUIVALENT FIELD||project_type_other|
|NO EQUIVALENT FIELD||is_classified|
|NO EQUIVALENT FIELD||rec_no|
|NO EQUIVALENT FIELD||project_status_code|