1 Reply Latest reply on Feb 28, 2011 10:18 AM by philmodjunk

    Simulating a spreadsheet



      Simulating a spreadsheet


      I work with a small company who desperately needs a database to manage their production, inventory, order processing & fulfillment, and shipping. But they are very much in the dark ages about information management.  I'm trying demonstrate what FM Pro could do for them. I know how to design most of the database but  One thing I don't understand is how to simulate spreadsheet-like function.  I have read about this capability and think it involves portals and charts.

      Specifically I want a screen so the warehouse guy can plan out his shipment on a form that has columns and rows.  Across the top are the 26 products the company carries, and down the side are listed the skids.by 1-20. This report would show what combination of products are on each of the skids, Each product would be totaled at the bottom along with the amount the customer ordered. It would look something like this:

                         Prod A       Prod B       Prod C     ...          Prod Z

      Skid 1            40

      Skid 2            15            15

      Skid 3                                           80                    35




      Skid 20                          35            90


      TOTAL          55              50           170                  35

      ORDERED     55              60           100                 

      DELTA            0             -10           -70

      The TOTAL row would be subtracted from the Inventory count for each product. (This is the only point at which info on this "spread sheet" would interface with the rest of the data in the program) The numbers assigned to each skid would also appear on the warehouse Pick sheet for each skid that skid's contents.

        • 1. Re: Simulating a spreadsheet

          This is called a "cross tab" layout. (There are many other "spreasheet" layouts that aren't cross tab.)

          You may want to think about whether your company is better served with products in rows with the skids in columns or whether the orientation you've posted is better. Generally speaking, it's much easier to add more rows than it is to add more columns, so you want to keep that fact in mind.

          In broad outline, you set up a list layout where each row is a record and each column cell is a one row portal. Setting up relationships and/or portal filters to control that can be a jot to do but it can be done.

          There is a somewhat heretical option you can also consider:

          Define 26 different number fields, each with summary fields to compute the column totals at the bottom. This approach would allow you to place the entire grid inside a portal where this functions a bit like a line items portal for an invoice. The catch is those dedicated fields really complicate any reporting where you want to see totals for each product in a more typical summary report format as well as other issues that crop up when you want to find and sort with this data on other layouts.