/size[quote]code:[/size]NUMBER DESCRIPTION CODE NEEDED ON HAND QUANTITY PALLETS NUM ON HAND SHORT ON ORDER DATE
01451 DC100 12:12X10:06X1: N00NN 2,816 993 1,823 986 2,750
01481 RSC19.94X14.25X4 N00NN 721 0 721 721 3,000 4-26- 7
02644 8 3/4 X 6 NESCEL N00NN 52,560 18,552 34,008 .99 074 97,200 66,150 5-10- 7
03183 B/GE 4 1/2X2 7/8X1 1 N00NN 3,240 964 2,276 074 10,237
03567 B/STA 7:10X4:10X1:8 N00NN 10,765 10,603 162 162 11,362 5-14- 7
897 6- 4- 7
03568 L/BBS 7:12X4:12X1:8 N00NN 7,280 7,071 209 209 8,618 5-18- 7
03616 B/STA 7 5/8X4 5/8X1 N00NN 20,000 233 19,767 19,767 16,290 4-27- 7
3,620 5- 1- 7
03881 L/BSS 7:12X4:12X1:03 N00NN 20,000 391 19,609 19,609 13,098 4-27- 7
6,642 5- 1- 7
04910 BURG/BRNZ 611 COVER N00NN 68,936 61,037 7,899 009 4,048 33,873 5- 4- 7
025 1,754 15,120 5-18- 7
035 4,527 11,340 6-15- 7
51,912 6-15- 7
04911 BURG/BRNZ 611X113 CA N00NN 59,256 45,698 13,558 009 1,848 44,980 5- 4- 7
025 1,508 11,340 6-15- 7
074 11,340 39,856 6-15- 7
That's my problem, I need all the data shown. Do I need a different version to achieve this? /b[/quote]Greg,
A version at PRO level would likely make life easier.
However there is a difference between needing all the data (perfectly reasonable though I suspect that one or two columns may not be important according to your sample - they might be in other samples of course) and what the needs are for the display of the data.
For example do you need the "Num" and "On Hand" fields as real data or as useful Memorandum fields for information but not analysis? They have the look of being information fields which are advisory rather than for analysis. In which case they could be captured in multi-line character field as a 'Memo'.
On the other hand since some products don't have "On Order" data maybe they need to be taken as individual data fields.
The bottom line is that you need to be clear about what the DETAIL levle for the data might be - the lowest level of analysis where the data component needs to be in its own field. If the answer to that is either or both of the column sets I have mentioned above then, yes, life will be interesting. Mainly because you effectively have 2 or maybe three reports in one output.
The classic suggestion here wouold be to extract the On Order info to a lookup table using a suitable 'key' to the product. In a separate process do the same with the On Hand data then recombine them into a common file. But if you do that using a lookup table in Standard you would need to manually populate the internal lookups (via cut and paste) each time you run the process. The Pro version would allow you to simply create external lookup files and then use them in the third model process.
That said you may still end up with a fudged result so I am wondering if there are alternatives.
So - the key is what the extracrted table need to look like. If it needs a populated line for every line that has either or both of an "On Hand" or an "On order" entry - and assuming it is the sort of report where one or the other of those will always be populated - it may be best to seek out an unusual approach to the problem.
Does your table need to have every field represented (even if empty) for all fields of every line of the report (expect blank lines?). WOuld that allow you to do the analysis you require?
If so I have some ideas to experiment with.
Here is a suggestion to try.
For your detail template trap select a full data line as the sample. Use the NUMERIC OR trap ( "|" ) on the last digit of both the SECOND On Hand column and the On Order.
That will make every line a detail line unless blank or a header.
Paint the field you require. Include everything from NUM to the right for sure. (For the other fields it will depend whether you want them to appear only once, as in the first line of an account group, or for every line by making them part of an append template. Either, or indeed both approaches, are possible. Some will certainly need to be in an Append template.)
The date in the last column may need a little work but we can come back to that.
Finish that template and add another as an APPEND. Trap for a NUMBER at the first position of the line. Paint the remaining fields you require. Save the template.
Review the table.
You should have everything you need though the date will likely still be a problem at this point.
Are you OK with deciding how to fix the date or would you like some suggestions for processing it into a recognised format? Its current format is pretty odd but there are a number of ways to fix it.
Does this help?
Edit to add: In fact the date was less of a problem than it appeared to be. Despite the unusual format Monarch did recognise the data as a date but the initial display I saw from the cut and paste text file created from the posted example truncated the output by 2 characters.
[size="1"][ April 27, 2007, 10:01 PM: Message edited by: Grant Perkins ][/size]