Are they actually exporting to an excel file or is this "spreadsheet" a view of the data inside FileMaker?
Either way, "as many columns as necessary" will be a potential problem. FileMaker can support as many related records (rows in a table view) as needed, but the number of columns will be finite. You can set things up to have a very large number of columns, but if an unusual case requires more than you have set up, you have a problem.
Thanks for asking. Yes, they want to view the data as an excel spreadsheet. They are analyzing the data for patterns of "part numbers" that occur together on invoices. (We aren't really dealing with invoices and line items, but it is easier to get the point across using a simple example that everyone can relate to.) The highest number of line items/part numbers that I have found is 16. Of course, there could easily be more as time goes by. My main problem is figurnig out how to get the portal data (part number) onto the same row as the invoice number.
THis is my first post, so I am not sure that I am doing this correctly...I am posting an answer instead of adding a comment. Maybe there is no "comment" action?
I am posting an answer instead of adding a comment.
Sigh... We've asked the Right Now programmers repeatedly to rename the Post a new Answer box to something better like "Post Reply" as you are using the forum interface correctly when you use it to add another comment to the thread. For the longest time, it was not even grammatically correct as it used to read "Post a Answer". They then fixed the grammar but still have it set up with text that is misleading as to the intended function of this text box.
In order to get the data into columns for export, you'll need calculation fields that copy the data from the related table.
GetNthRecord ( RelatedTable::Field ; 1 )
will return the value of Field from the first related record in RelatedTable, so you could define a long series of calculation fields with GetNthRecord, specifying 1, 2, 3 and so forth as the second parameter passed to that function.
But what you describe, looking for part number patterns, sounds like something that could be set up on a list view layout in FileMaker and that, in turn might greatly simplify the whole process. With such a layout a horizontal portal technique could be used to produce multiple columns of data from a related table. A horizontal portal is a row of single row portals that are either setup with initial row settings of 1, 2, 3... or that use portal filter expressions to select which related record appears in that column.
I knew there had to be a technique that would allow me to do a horizontal portal! I'm new to FMP development, but I'll see what I can discover about this. So far I have found tutorials for fp5 and fp7. Can you direct me to any documentation that can help me figure out how to set up a horizontal portal? This would be a much better solution than saving the data into a spreadsheet.
If you know how to set up portals, this sums up what you need to know:
initial row settings of 1, 2, 3... or that use portal filter expressions to select which related record appears in that column.
In portal setup, there are two settings that you can experiment with. You can set the initial row setting and number of rows setting to different values to see how you can set up single row portals that only display the 1st, 2nd, 3rd, etc rows. Or you can play with the portal filter expressions if you want the portal record in "category A" in column 1 and the portal record in "category B" in column 2.
Phil, you gave me enough information so that I was able to accomplish my objective. Can't thank you enough! There is one issue remaining. When I look at my regular "invoice" layout, I see invoice information on the top of the screen, with line items appearing in a "normal" portal. The portal rows are sorted ascending by part number. When I create my collection of single row portals, the individual part numbers appear in essentially random order. Since each "column" is a single filed portal, can you suggest a way to get the columns to appear in sorted order? As I mentioned before, the user wants to analyze the data looking for patterns of "part numbers" that appear on multiple invoices, this is most easily accomplished by having the "part numbers" in ascending order. Any suggestions?
Just sort all of your one row portals just like you did in the multi-row portal on your invoice layout.