Scenario: the customer had a list of parts (coming from a BOM, but that's not important), where each part has several manufacturers. He wanted to have all of the manufacturers listed in the same row as the part - what we would call a tabular report in Priority, or a pivot table in Excel. I told the customer that this was not a good idea, as the manufacturers for one part would not necessarily be the same as the manufacturers for another part; the tabular report would create a column for every manufacturer, so the resulting report would have many columns and would also be sparse. Not a good idea. Then he told me that he wanted to send the output to Excel - this made the tabular report a really bad idea, as the export to Excel causes the tabular report to lose its distinguishing character: it becomes a normal columnal report, with each manufacturer on a different line.
What can be done?
I hit upon the idea of numbering each manufacturer for each part: let's say that for a given part, there are four manufacturers. These will be numbered 1, 2, 3 and 4, the number being stored in a private field. Another part has three manufacturers: these will be numbered 1, 2 and 3. The actual number and the order are meaningless. In the report, there is one column for the part number, one for its name, one for manufacturer #1, whoever that might be, one for manufacturer #2, etc. Of course, there are many more fields, but the result is a quasi-tabular report that can be exported to Excel without problem.
The very easy parts were adding the private field to the PARTMNF table and form. The easy part was writing a procedure that would iterate over all the parts that have manufacturers (i.e. the PARTMNF table), assigning the numbers and updating the table. Whilst writing the previous sentence, I realise that I don't need to incorporate the PART table in this procedure; it can be based entirely on PARTMNF. This procedure can be run every day, updating only those lines in PARTMNF whose private field's value is 0.
The hard part was writing the report. Assuming that the report is limited to four manufacturers per part, the PARTMNF table has to be joined four times; just to make things harder, these joins should be left joins, as there is no guarantee that every part has four manufacturers. Not only that, a left join is also required between PARTMNF and MNFCTR in order to obtain the name of the manufacturer (is it possible to have a manufacturer's part name without the manufacturer itself having a name??). After working hard on this yesterday evening, I finally got the report working.
I hope that the above is comprehensible. There's no easy way of listing a report in order to display how this was done, and anyway there are many more fields in the report than I have described.
Edit from later the same day: another client had a report that he also wanted to turn into a quasi-tabular form, so I had the solution at my fingertips!
