Reporting and Database Features

Providing The Best Possible Reports and Information Is Our Top Priority

For many software packages, reporting is just a by-product of the data that is input and collected.

The PRIMARY purpose for input programs and data files in Produce Magic Software is to create the data needed for reporting. We take great pains to structure our data fields, files and indexes to provide the best possible reporting capabilities for you.

If the data has not been stored in the data files, you will never be able to get it out in reports! In many cases, we spend more time designing data files and indexes than writing the programs that use those data files. Proper database design is crucial to the success of a system like Produce Magic Software.

The summary information we provide makes it much easier for produce companies to control their costs and know exactly what their profit and loss is on each product from each supplier, and on each product sold to each customer.

We write custom Grower Accounting programs for many of our customers, to give them totally automatic grower accounting that shows exactly what they need for each grower/supplier. That obviously saves a great deal of time and labor cost.

The required level of detail is not usually available from software packages, so many produce companies have to do part or their entire grower accounting manually in a spreadsheet or other program.

Manual grower accounting is extremely labor intensive and prone to errors. Since the information is not easily reproduced and is not as accurate as it should be, produce companies often overpay their growers intentionally to avoid any question. If detailed, totally accurate grower accounting information is available when growers are paid, in many cases it would reduce what is owed to the growers.

We would be happy to show you the actual data files, indexes and inner workings of our programs in person, so you can see that we are able to provide ANY reporting capability that you may need now or in the future.

Very Detailed Information Is Stored In The Data Files

Produce Magic Software provides extremely detailed information because of our detailed file structure and our excellent use of indexes.

Our Sales Order and Purchase Order Line Item files both contain the same data fields as well as our Sales Order and Purchase Order Header files. These are totally separate data files, but we have made the fields basically the same for Purchase Orders and Sales files, to make it easier to write programs for one and copy them to use them with the other files. That features greatly reduces programming time.

The large number of fields may seem like overkill initially, but keep in mind that our Number One Goal is to provide the ability to get almost anything out of our system. You can only get information out of the system that has been put into it and stored in a manner that it can be retrieved efficiently.

The Proper Use Of Indexes Is The Key To Fast Excellent Quality Reports

Many programming languages allow only 3-5 indexes per data file, which probably seems like enough indexes to most people. Pervasive SQL data files provide many more indexes for each file, which most people think is more than we could ever use, but we find it is never enough for the main data files.

To increase our effective number of indexes and enhance our reporting capabilities, we include a lot of combined fields in our main data files and put indexes on those special fields. For instance, the Sales and Purchase Order Line Item files contains about 70 combined data fields, whose only purpose is to combine other data fields so the combined information only uses one of the many indexes.

All of the information in those 70 combined data fields is also contained in the separate data fields in the same file. The redundant information contained in the 70 combined fields is included in the data files only to provide better indexing and reporting capabilities. That shows how serious we are about providing outstanding tracking and reporting in our programs.

INSTANT Screen Displays or Printed Reports For One Week Or One Month

Produce companies input their Week End Day of the Week and each of their Month End Dates in Produce Magic Software data file. That provides the information needed to insert the Week End and Month End date into every Sales Line, Purchase Order Line, Sales Order Header, Purchase Order Header, Inventory Lot, etc.

We have customers who ship about 10,000 line items per day. (1000 – 1500 Sales Orders with 8-10 line items per order). After three years they could have TEN MILLION records in their Sales Line Item file. That would bring most database systems to their knees. With ten million records in the file, if you wanted to get a grower report for one week or one month in order of the ten separate fields used in this combined field, it could take a super computer DAYS to sort the data records before it even started to print. Using the combined field definitely makes reporting on that information MUCH faster then searching on ten separate fields, but even using that combined field then the Date field in an index, the results would be very slow for a large data file. Even getting all ten of those fields INSTANTLY sorted in that order, a grower report for one week or one month out of the three years of data in a large data file could take many hours to print.

The reason is that three years of information would have to be eliminated for each combination of that combined data field. Having the data initially sorted in order except for the date, makes it fifty times faster than not starting with information sorted in order of that combined index, but it would still take a very long time to eliminate the non-needed date records. We use that same combined field explained above (and many others) in combination with our Week End Date and Month End Date for indexes.

Our Week End and Month End dates let us INSTANTLY display or print a report in order of the combined field, for a One Week or One Month period, even if data file contained one hundred million records. The first field in the index is the Week End or Month End Date field.

We would have needed over a hundred separate (non-combined) fields to get the same indexing as we obtained with those twelve index elements. The reason Original Purchase Order Number and Original Inventory Lot number were used in that combined index was to include ALL repacked Lots from that Original Inventory Lot. As Inventory Lots are repacked, they remain linked back to the Original Inventory Lot (that they arrived on). This allows providing growers with accurate accounting reports that include all repacking costs and sales for each Original Lot that you receive.