Jump to content

Export summaries to flat file Excel


PiedPiper
 Share

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

Recommended Posts

I kinda got in trouble today. Owner wanted summary of all products sold by Customer by product. I created report and put customer and product both in sub-part above. The report displayed right. But he wanted it as straight data, so I exported it.

When importing into Excel, each sub part only listed once on the colume - rest blank until the next customer/product displayed. I worked on it for hours trying to export. I even added customer and product into the second subpart and only exported that subpart - nothing worked. and finally copy/pasted all through the spreadsheet to 'fill in' this information manually. It wasn't ready for his meeting and Im in deep [censored].

How can I take summaries and export them so they display as flatfile line items? I thought script to create records in new db - not sure that would work. I also needed to include the salesperson name - which would be the same on each so I just put it in the sub-part also. That worked. Why wouldn't the info 'category' sub'd and sorted by - not duplicate itself on each line but other stuff would.

Hope this made sense. If not, I'd be happy to try to explain it better. It's been one of those days and I think I hate exporting summaries. I clicked summary and clicked all fields displayed - then tried unclicking those fields - nothing made a difference. A bit of direction here please, all you smart people?

Pete

Link to comment
Share on other sites

I ran into this. FileMaker is trying to help by outputting the data pretty much as it would for a Subsummary report. The trouble is the "flat-file" systems have to have the data in every row.

You'll notice that the missing data is in the fields you sorted by. An easy work-around is to duplicate that field(s), then sort on the duplicate instead. Or include the duplicate in the export data instead. You can make the duplicate Unstored, so it doesn't add much to the file size.

There may be a better way, but that's what I remember right now.

Link to comment
Share on other sites

Hi Fenton,

Okay, I've been working on this. Could you explain more by:

"An easy work-around is to duplicate that field(s), then sort on the duplicate instead. Or include the duplicate in the export data instead. You can make the duplicate Unstored, so it doesn't add much to the file size."

When I copy and paste the duplicate field on the form, it doesn't come up as a duplicate to sort on.I pasted it in both sub parts. Unstored? That's in definition fields isn't it? How can I make it both. I changed Customer field to indesx on and unstored but it didn't like it and when I paste the field on the layout it seems the same. Well, somethings wrong that I'm missing. Can you just say some more about this. I don't quite understand. Thank you. grin.gif

Pete

Link to comment
Share on other sites

I wasn't talking about the layout, I was talking about "duplicating" the field in Define Fields. What I meant was create a calculation field2, make it: = field1. Then you can sort on field1, but export field2. Since FileMaker is only doing the Summary thing on the field you actually sort on, you should not get "blanks" when you export the other field. At least that's what I remember; let me know if it doesn't work.

Link to comment
Share on other sites

oh all makes sense now. Amazing how one little word 'duplicate' can send someones thinking in the wrong direction. Thanks Fenton, I'll try it. I'm sure it'll work because the only fields that would only show one line were the ones being sorted as you said. If I run into problems I'll let you know but I don't think I will. smile.gif

i'm trying to finish another project with backgrounds and then I'll turn back tothis issue. Owner will want more Excel stuff on Monday and I need to be ready for him. crazy.gif Thanks again.

Pete

Link to comment
Share on other sites

This topic is 6539 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

×
×
  • Create New...

Important Information

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