Jump to content

Exporting fields' headers


This topic is 3117 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello,

We have a table with more than 15000 rows (museum inventory application). We used to export large amount of rows (some times like 900) to modify several fields in every rows then import it back (with version 8.5 of fmp). Now that we migrated on FMS14, it appears that it is not possible to export the fields' names in the tab file as first line. Is there a way to get the headers in the exported file ? if not please consider adding the possibility in next release.

Thank you

Thomas.

Link to comment
Share on other sites

Thank you for your answer. This is effectively 15,000 records for the moment. Should go to 23,000 when the base will be complete. Each record has around a hundred fields. This is a museum database where operators will extract a whole collection for inventory to add the same data in some fields using excel, then import it back to update the database. They used to proceed like that because they have big collections, avoiding a long update process.

Excel does not open the .mer file correctly because the data can have ';' inside. and " is replaced with "".

Thomas.

Link to comment
Share on other sites

Now that we migrated on FMS14, it appears that it is not possible to export the fields' names in the tab file as first line.

I don't know of any changes in the supported export formats. Field names were never exported as part of a tab-delimited file.

If you want a custom export format, you could built one using an XSLT stylesheet and exporting as XML - however this would not work in WebDirect.

Link to comment
Share on other sites

With FMP 8.5, they were exporting to Excel format. But WebDirect does not export to Excel.

Right. The problem is not upgrading to v.14, but downgrading to WebDirect.

If you cannot find any other way to solve this (e.g. through a script performed on the server), you could hack it by importing the records to a dummy table that has a dummy record for the field titles, then exporting from there.

Of course the best solution would be to improve the performance, so that updates can be made in situ.

Edited by comment
Link to comment
Share on other sites

Right. The problem is not upgrading to v.14, but downgrading to WebDirect.

If you cannot find any other way to solve this (e.g. through a script performed on the server), you could hack it by importing the records to a dummy table that has a dummy record for the field titles, then exporting from there.

Of course the best solution would be to improve the performance, so that updates can be made in situ.

I will write a tool to convert .mer files to excel. It is the easiest way for me. Thank you.

Link to comment
Share on other sites

I'd be curious to see a solution where the data entry is easier in Excel than it is in FM. What are you doing with the inventory in FM? ( not discouraging it's use, just wondering what added functionality you are adding to the vanilla spreadsheet that helps you manage the inventory )

Especially if you have people working on the database between the export and the import, it can open a large loophole for errors and overwritten data.

Link to comment
Share on other sites

I will write a tool to convert .mer files to excel. It is the easiest way for me. Thank you.

I think you can just export your .mer file, rename to .csv and Excel will open it right up.

Link to comment
Share on other sites

I'd be curious to see a solution where the data entry is easier in Excel than it is in FM. What are you doing with the inventory in FM? ( not discouraging it's use, just wondering what added functionality you are adding to the vanilla spreadsheet that helps you manage the inventory )

Especially if you have people working on the database between the export and the import, it can open a large loophole for errors and overwritten data.

Well, this is a museum inventory with more than 15000 records and 100 fields in one table. They currently proceed to update data to collections. Each item has an inventory number.  Some collections are small so they update directly in WebDirect. Some are big (like the Mochica collection with 112 items), so they extract items they need in an excel file, then they update a field with the same value to all records (by dragging the mouse in the excel column), then they import back the file. The employees are not computer's friends... I has to be simple. And I have to find a solution to avoid them to enter the same value 112 times (or more). We use extraction as well to send data to the national museum agency or to print the annual mandatory paper version of the inventory.

Only one employee works at a time on one collection, so there will (should) be no problem with overwritten data. In any case we have a daily backup.

Finally as a solution I wrote a little tool to convert from '.mer' to Excel.

I found another strange thing : when I export data to '.mer' format from WebDirect menu, I get it in UTF-8. That's fine. But if I want to export data to '.mer' file from within a script, I have only the ANSI or Windows encoding format !

Is it possible from a script to export data locally to the server, then run the tool to convert the file to finally download it to the user ? This would be a very good solution for me.

Thank you

 

I think you can just export your .mer file, rename to .csv and Excel will open it right up.

I tried but there are problems within the ';' character that can be found in the data itself, letting excel thinking that it is a column separator.

Link to comment
Share on other sites

From FileMaker if you truly need that value in every record, there are a lot of options to automate that process. It's FileMaker's specialty.  That would be a lot less fragile than export, update, import ( hopefully no errors or partial imports ).

Link to comment
Share on other sites

From FileMaker if you truly need that value in every record, there are a lot of options to automate that process. It's FileMaker's specialty.  That would be a lot less fragile than export, update, import ( hopefully no errors or partial imports ).

Excellent, I will have a look. Thank you.

Link to comment
Share on other sites

they extract items they need in an excel file, then they update a field with the same value to all records (by dragging the mouse in the excel column), then they import back the file.

To me, this sounds like your real issue is with the structure, and all of this is just an elaborate workaround. While it's certainly possible to script such operation in Filemaker itself, it should not be necessary. Instead, there should be a parent record where this value would be updated once and for all (literally).

 

100 fields in one table.

This also raises a flag. There are very few objects that require 100 fields to describe them. I suspect you are using fields where records in a related table are called for.

  • Like 1
Link to comment
Share on other sites

Excellent, I will have a look. Thank you.

The feature seems not present in WebDirect.

To me, this sounds like your real issue is with the structure, and all of this is just an elaborate workaround. While it's certainly possible to script such operation in Filemaker itself, it should not be necessary. Instead, there should be a parent record where this value would be updated once and for all (literally).

 

This also raises a flag. There are very few objects that require 100 fields to describe them. I suspect you are using fields where records in a related table are called for.

I had to work with what has been designed 10 years ago... And I don't have the possibility to re-engineer it. For the fields, there is a lot because some are common, some are specifics following the item type (painting, sculpture, tools...), and the designer putted everything in one table...

Link to comment
Share on other sites

This one : http://www.filemaker.com/help/12/fmp/html/add_view_data.4.48.html

[Records menu > Replace Field Contents] does not exist in WebDirect 14

I believe the suggestion was to script this for the users. If your users are not, as you put it, "computer's friends", then you certainly don't want them to be able to use Replace Field Contents on their own.

Link to comment
Share on other sites

I believe the suggestion was to script this for the users. If your users are not, as you put it, "computer's friends", then you certainly don't want them to be able to use Replace Field Contents on their own.

+10

@Auch info - Lookup script steps for Loop and Set Field.

Edited by Josh Ormond
Link to comment
Share on other sites

This topic is 3117 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.