Jump to content
Make Add-ons Better

Exporting fields' headers

Auch info

By Auch info
in FileMaker WebDirect

 Share

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

Recommended Posts

Auch info Rookie

Auch info

Posted
Posted

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.

Lee Smith

Lee Smith

Posted
Posted
  On 9/3/2015 at 2:18 PM, Auch info said:

We have a table with more than 15000 rows (museum inventory application).

Please tell me that you mean 15,000 Records and not Rows?

Auch info Rookie

Auch info

Posted
  • Author
Posted

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.

comment Grand Master

comment

Posted
Posted
  On 9/3/2015 at 2:18 PM, Auch info said:

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.

comment Grand Master

comment

Posted
Posted (edited)
  On 9/4/2015 at 7:43 AM, Auch info said:

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
eos Mentor

eos

Posted
Posted
  On 9/3/2015 at 2:41 PM, Lee Smith said:

Please tell me that you mean 15,000 Records and not Rows?

Excel::row = FileMaker:.record
Excel::column = FileMaker::field

:)

Auch info Rookie

Auch info

Posted
  • Author
Posted
  On 9/4/2015 at 8:06 AM, comment said:

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.

Josh Ormond Mentor

Josh Ormond

Posted
Posted

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.

xochi Mentor

xochi

Posted
Posted
  On 9/8/2015 at 8:56 AM, Auch info said:

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.

Auch info Rookie

Auch info

Posted
  • Author
Posted
  On 9/8/2015 at 1:20 PM, Josh Ormond said:

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

 

  On 9/8/2015 at 11:29 PM, xochi said:

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.

Josh Ormond Mentor

Josh Ormond

Posted
Posted

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 ).

Auch info Rookie

Auch info

Posted
  • Author
Posted
  On 9/11/2015 at 2:20 PM, Josh Ormond said:

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.

comment Grand Master

comment

Posted
Posted
  On 9/11/2015 at 8:01 AM, Auch info said:

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).

 

  On 9/11/2015 at 8:01 AM, Auch info said:

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
Auch info Rookie

Auch info

Posted
  • Author
Posted
  On 9/11/2015 at 2:59 PM, Auch info said:

Excellent, I will have a look. Thank you.

The feature seems not present in WebDirect.

  On 9/11/2015 at 4:17 PM, comment said:

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...

Josh Ormond Mentor

Josh Ormond

Posted
Posted (edited)
  On 9/16/2015 at 6:51 AM, comment said:

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
Josh Ormond Mentor

Josh Ormond

Posted
Posted

Thanks Michael. Are there any limitations to a besides a forced "Perform without dialog"? I've only used WD a limited amount and haven't tested that script step yet.

This topic is 3493 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
 Share
Go to topic listing
×
×
  • Create New...

Important Information

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

  I accept