4 Replies Latest reply on May 20, 2014 11:44 PM by NathanVeitch

    Displaying records horizontally like in Excel

    NathanVeitch

      Title

      Displaying records horizontally like in Excel

      Post

      Ok, this is driving me insane. What am I missing here??? Im sure it is not this complicated. I have one excel spreadsheet that has data per product ordered by each branch. So in my mind that should go into a join table between branch and product but for some reason I have hit a brick wall.
      In Excel it looks like this:
      .................Branch 1 | Branch 2 | Branch 3 ...
      Product 1 ..........5 | .........3 ..... |.......3
      Product 2 ..........2 | .........4...... |.......5
      ...

      and the user can scroll vertically across the 600 odd branches and horizontally through the 120+ products. The only way I have been able to get this working is by creating a field for every product and then the record is based off the branch code. But the user needs to approve or decline orders based on the stock on hand in the products table. Also they need to see the minimum quantity of each product when approving/declining request per branch. The products are all updated daily via a URL link to their suppliers DB, which is working perfectly. 

      I have setup portals to display 5 of the branch records at a time and so far on my sample data this is working nicely, but if I continue down this rabbit hole I am sure I am going to be in a world of hurt, as each product has a field for the code, name, order amount and sum of, approve amount and sum of, declined amount and sum of, min and on hand qty and product code. This would mean that my table could potentially have over 1000 fields, and then I would need to do sorting, finds, charting etc, plus move the order to a history table for a 3 month report. 

      I originally did this on a branch by branch import, but then my client said that she gets all the orders in one excel spreadsheet and not individually by branch. I need to be able to import the one excel spreadsheet and not the individual branches. 

      I must say that I have a way of trying to re-invent the wheel, so maybe I am thinking to much about this, as it cant be that complex. It's just that I am going crazy trying to work this one out, and walking away for a breather isn't helping either 

        • 1. Re: Displaying records horizontally like in Excel
          philmodjunk

               FileMaker is not Excel. It is far more easy and more flexible to list records vertically (up and down) instead of horizontally (side to side)

               What you describe is called a "cross tab" report so you can research that term in FileMaker resources to see other descriptions of how you might set such a report up.

               The simplest way to do this is with a method called the "horizontal portal". It's really a row of individual one row portals. Either a portal filter on each portal controls which record appears in it or the portals are set up so that the first portal has an initial row setting of 1, the next and initial row of 2, the third an initial row of 3...

               But doing that for 600 branches on one layout seems very extreme. Given that you have many more Branches than Products, it seems an add design to have 600 columns and 120+ rows--sounds like a lot of scrolling sideways to see data. Sure you can't convince your client to arrange this so that the products form the columns and the branches form the rows? That's still a lot of layout design and likely to a be a slow to update layout but then you are looking at 120+ one row portals instead of 600.

               This, BTW, has nothing to do with how the data is imported, only how it is displayed.

               If the excel spread sheet data is always formatted in the format you describe, then yes, you are looking at a table with either 600 fields or a multiple of 600 fields if there are several columns of data for each branch. You might, however, be able to create that table by doing a drag and drop of the excel file onto the FileMaker application icon or by importing one such file into your database with the "new table" option selected for the target table.

               But after importing, I'd give serious consideration to using a script to pull that data from this table into one better structured for use in FileMaker.

          • 2. Re: Displaying records horizontally like in Excel
            NathanVeitch

                 Hi PhilModJunk,

                 Thank-you for this. I am still playing around with ideas, as I am sure I am over thinking this. I was using a sample of the data for testing and was able to get the simulated scrolling using 5 portals. The scary thing was that I had ended up using so many fields per product. I am trying to look at this as the Invoice--InvoiceLine--Product structure as i am sure if I break it down this is how it should look. Cause when I did the branches separately it worked.

                 I was going to try, as you said, import the whole table using the Import feature and set it to create the table with the first row being the field names. This would give me the field names = the branch codes and the first field would be the product code. Then i should be able to script the data into a  join/lines table to do my calculations and base the table off the branches and the portal off the join table with the products listed individually. 

                 Then when it comes to displaying the data, would I be able to use a List  View based off products and then try the 5 portal method but base them off the join table??? 

                  

            • 3. Re: Displaying records horizontally like in Excel
              philmodjunk
                   

                        The scary thing was that I had ended up using so many fields per product.

                   But you should not need multiple fields for products. What you show would have one record for each product, not multiple fields.

                   Each row shown in your example would be a different record shown in List View, with a portal to the relevant records for data linking that product to a specific branch.

                   This requires a table with one record for each product.

                   And if you can transform the matrix so that you have products in columns and Branches in Rows, this becomes simpler.

                   And you might avoid having so many columns in such a set up by showing only one category of products at a time by adding a drop down list or pop up menu formatted field in the header for selecting a category. This would reduce the need to scroll the layout horizontally to see more columns of data.

              • 4. Re: Displaying records horizontally like in Excel
                NathanVeitch

                     I still don't know what I was thinking when I started this. I did however have a major face palm moment when I realised what I was doing. And to think I have a table for branches and a table for products and i couldn't see how to works this. I have done this many times before, but for this project I just went off on some very obscure tangent. 

                     Thank-you again for the help, it is always very much appreciated.