3 Replies Latest reply: May 15, 2014 10:07 AM by Olly Bond RSS

    Record data inconsistent - footers isn't the answer

    Sue DeWitt

      Below is my report sample.  The first 5 lines are the supplier detail records.  For the "Acorn Inc" record, there is additional Site information.  

        *There can be none, one, or multiple sites for each supplier.  ("Acopian Technical" has no site info.)

        *For each Site, there can be one or more site uses (always at least one) 

        *Under each site there can be none, one or multiple Contact Names

        *There can be one or no bank account (never multiple)

       

      If "Acorn Inc" had a second site, that site information would start after the first site's bank info.

       

      Footers don't work since it adds to each detail record.   How do I capture all of the variable details for each supplier record?

       

      Thanks in advance for any assistance - Sue

       

      Supplier Name: Acopian Technical Company                  Taxpayer ID:                                  Status: Active                 

      Supplier Num: 8044                              Tax Registration Num:                           Creation Date: 22-JUL-05              

      Supplier Type: Electronics                      Income Tax Reportable: No                           Created By: BNEWELL                

           One Time: No                                    State Reportable: No                          Update Date: 24-JAN-06              

            PO Hold: No                                     Income Tax Type:                              Updated By: ROSE.FIORE                                                                               

      Supplier Name: Acorn Inc                                  Taxpayer ID: XX-XXXXXXX                       Status: Active                 

      Supplier Num: 11533                             Tax Registration Num: XX-XXXXXXX                Creation Date: 02-DEC-05              

      Supplier Type: Investigator                     Income Tax Reportable: Yes                          Created By: BNEWELL                

           One Time: No                                    State Reportable: No                          Update Date: 12-JUN-06              

            PO Hold: No                                     Income Tax Type: MISC6                        Updated By: KATIE.GOODFELLOW                                                                               

      Creation     Created        Update       Updated             

              Site Name       Address                             Status        Date         By             Date         By                  

              -


      -


      -


        -


          -


        -


          -


                

              1770 KIRBY PARK 1770 KIRBY PARKWAY, SUITE 400       Active        02-DEC-05    BNEWELL        15-JAN-08    SHAMEEZA.ALL        

                              MEMPHIS TN 38138                                                                               

      United States                                                                               

      Voice  :  901 ###-####                                                                               

      Fax  :  1-901 ###-####                                                                               

      Hold      Hold         Hold                            

                                   Payment     Pay        Payment  Payment      Pay   Unmatched Unvalidated  All                             

              Site Uses            Terms       Group      Priority Method       Alone Invoices  Invoices     Payments                        

              -


      -


      -


      -


      -


      -


      -


      -


         -


                                

              Pay                  Immediate   US SUPPLIE 99       Electronic   No    No        No           No                              

              Purchasing                                                                               

      Contact Name: Sandi xxxxx                  Position:Account Associate     Telephone:(901) ###-#### Status:Active                                                                               

      Bank Account Name: Acorn Inc                                                     Number: #######                               

                   Curr: xxx                    Primary: Yes          Effective Date:  15-JAN-08 -                                           

      /code

        • Record data inconsistent - footers isn't the answer
          Olly Bond

          Hello Sue,

           

          I fear that the guru trap approach won't help with multiple sites, so we might need to look at a two pass solution. In order to get it right, please could you let us have an idea of how the output should appear?

           

          Supplier A, Site 1, Use X

          Supplier A, Site 1, Use Y

          Supplier B, Site 1, Use X

          Supplier B, Site 1, Use Y

          Supplier B, Site 2, Use X

          etc

           

          or something like:

           

          Supplier A, Site 1, Use X, Site 1, Use Y

          Supplier B, Site 1, Use X, Site 1, Use Y

          Supplier B, Site 2, Use X

           

          etc?

           

          Best wishes,

           

          Olly

            • Record data inconsistent - footers isn't the answer
              Sue DeWitt

              Olly,

               

              Each site should only have one record, so the second presentation.  (BTW, I'm using v9.)

               

              Supplier A, Site 1, Use X, Site 1, Use Y

              Supplier B

              Supplier C, Site 1, Use X, Site 1, Use Y

              Supplier C, Site 2, Use X

               

              Thanks,  Sue

                • Record data inconsistent - footers isn't the answer
                  Olly Bond

                  Hello Sue,

                   

                  These kind of projects can be done in Monarch, but you've got to deal with more than one level of detail.

                   

                  Here, you'll have to capture the Contacts, the Uses, the Sites and the Suppliers with detail templates, which means four models.

                   

                  Let's start by building models and projects which capture these, and which all capture the Site as an Append.

                   

                  Now assume that we have got clean Access/Excel tables with this data.The next step is tying them together... Not every supplier has a Site, or any other data, so they'll need to be the key to give the output we want.

                   

                  Table: Suppliers (made by modelling with Suppliers as Detail)

                   

                  Supplier A

                  Supplier B

                  Supplier C

                  etc

                   

                  Table: Sites (made by modelling with Sites as Detail)

                  Supplier A, Site 1, 

                  Supplier A, Site 2

                  (nothing here for Supplier B)

                  Supplier C, Site 1

                   

                  Table: Uses (made by modelling with Uses as Detail)

                  Supplier A, Site 1, Use X

                  Supplier A, Site 1, Use Y

                  Supplier A, Site 2, Use Z

                  (nothing here for Supplier B)

                  Supplier C, Site 1, Use Z

                   

                  Table: Contacts (made by modelling with Contacts as Detail)

                  Supplier A, Site 1, Contact Albert

                  Supplier A, Site 1, Contact Bill

                  Supplier A, Site 1, Contact Charlie

                  Supplier A, Site 2, Contact David

                  Supplier A, Site 2, Contact Edward

                  Supplier A, Site 2, Contact Fred

                  Supplier C, Site 1, Contact Zebedee

                   

                  We can try a couple of approaches here to bring these together. At this stage, we just need to check that we're not going to hit the limits of Monarch. Is it possible that one Supplier might have more than 250 pieces of information recorded against them? And would all the information written about one supplier exceed 4000 characters?

                   

                  Best wishes,

                   

                  Olly