6 Replies Latest reply on Dec 19, 2014 6:43 PM by KandraScott

    How Do I Build a Report Based on a Single Criteria Relationship

    KandraScott

      Title

      How Do I Build a Report Based on a Single Criteria Relationship

      Post

      Hello,

      I have a created a single criteria relationship.  Corporate Account Class to Corporate Account Class.

      I included a screen shot below.

      Table one Corporate Accounts contains all of the Account numbers that belong to A Corporate Account Class.

      Table two Corporate Accounts Pricing contains all of the Pricing for a SKU within a Corporate Account Class. 

      I have a portal on my Corporate Accounts layout.  The portal is based on the Corporate Accounts Pricing table.

       

      The portal comes out perfectly.  A list of all the chemicals sold to that account number and the correct price.

      The problems is I can't get a report that prints this information.

      If I set up a report based on Corporate Account Pricing.  All of the SKUs are listed, but only for 1 account per Corporate Account Class.

      So lets say Corporate Account Class ARG has 4 accounts under it.  

      Only the set of chemicals for account 195 prints on the report.

      If I base the Report on the the Corporate Accounts table. All 4 accounts print, 195, 196, 198, and 199 but it only shows the first SKU 1347-5150 for the Class repeated. There are 9 other SKUs that should be listed.

      How can I get the report to show all the Account #s and SKUs.

      I even tried just an Excel export of all the Fields on the Corporate Accounts layout.

      That printed all the SKUs, but every line between each Account # in the Account Number column was blank.

      There are too many of these to copy and paste the missing information in.

      Thanks in advance for any help you can give me.

       

       

       

       

       

      Single-Relationship.jpg

        • 1. Re: How Do I Build a Report Based on a Single Criteria Relationship
          philmodjunk

          I'm not sure how you want to include Account Numbers in combination with your SKUs and pricing. Since your relationship matches only by class, it does not appear that there is ANY link that pairs a specific account number with a specific SKU in the pricing table.

          So lets say Corporate Account Class ARG has 4 accounts under it.

          Does that mean that you have 4 records in Corporate Accounts with a Class of ARG, but a different account number?

          If so, given your relationship, the only report format that makes sense to me would look like this:

          Account Class ARG
              Account Numbers: 195, 196, 198, 199

            SKU detail
            SKU detail
            SKU  detail
            and so forth

          Account Class

          Is that what you need?

           

          • 2. Re: How Do I Build a Report Based on a Single Criteria Relationship
            KandraScott

            Sorry but that is not what I am after.

            I need it to look like my portal for each account. But I want it to list every account on the report.

            A full list of every Account and SKU.  

            Essentially it would look like an Excel sheet.

            What I am trying to do List Every Account in a Class and Every SKU for that class

            AN   SKU            Class

            195 1347-5150  ARG

            195 1347-5507 ARG

            196 1347-5150 ARG

            196 1347-5507 ARG

            198 1347-5150 ARG

            198 1347-5507 ARG

            199 1347-5150 ARG

            199 1347-5507 ARG

             

            That is just a small example.  

            The report I am having to put together has to be separate lines, because I am going to export to out as an Excel file.

            That file will then be given to another company who is adding it to their own database.

            I was trying to use Filemaker to keep from having to copy and paste thousands of lines in Excel. 

            I am so close to what I need, but exporting to Excel from is leaving all the repeats between account numbers blank.

            Like this:

            AN   SKU            Class

            195 1347-5150  ARG

                   1347-5507 ARG

            196 1347-5150 ARG

                    1347-5507 ARG

            198 1347-5150 ARG

                    1347-5507 ARG

            199 1347-5150 ARG

                    1347-5507 ARG

             

            Hope this is a better explanation of what I am trying to do.

             

            • 3. Re: How Do I Build a Report Based on a Single Criteria Relationship
              philmodjunk

              I think that you'd have to use a script to generate a completely new table before you could get that result from your file. A looping script could build such a table for export purposes.

              • 4. Re: How Do I Build a Report Based on a Single Criteria Relationship
                KandraScott

                How would I do that?

                I know what a looping script is, but would I loop the SKUs or the account # for each Class.

                 

                I don't understand why Filemaker can't make a report just like my portal. 

                The relationship is the same isn't it?

                 

                I thought it might help if I attached a screen shot of my portal.

                This is account 195 and you can see it is pulling all of the Products associated with the Class ARG.

                All I really want it to do it print the portal of each account #

                Thanks again for your help with this.

                • 5. Re: How Do I Build a Report Based on a Single Criteria Relationship
                  philmodjunk

                  You'd need to find all records in your accounts table and loop through them, generating a set of SKUs records for each and adding he account data to additional fields in this export field to populate all of the columns.

                  Go to Layout ["Accounts" (Accounts)]
                  SHow All Records
                  go to Record/request/page [first]
                  Loop
                     Set Variable [$AccountNo ; value: Accounts::AccountNo ]
                     Go To Related Record [Show only related records; From table: SKUs; Using layout: "SkUs" (SKUs) ]
                     Go to Layout ["ExportTable" (ExportTable) ]
                     Import Records  ---> specify the new export table as the target table and the SKUs table as the source table.  
                     Replace Field Contents [no dialog; ExportTable::AcctNo ; $AccountNo ]
                     Go to Layout [original Layout ]
                     Go to Record/Request/Page [Next; exit after last]
                  End Loop

                  The above script assumes that there are related SKUs records for every account record. If this is not the case (You may have just started setting up a new account record, for example), you'd need to add more code just before the GTRR step to avoid all but the go to next record step in cases where there are no related records.

                   

                  • 6. Re: How Do I Build a Report Based on a Single Criteria Relationship
                    KandraScott

                    Phil,

                     

                    Thank you for the reply.  I was so deep into my project yesterday and today I didn't notice you answered me.

                    I used your advice to make a loop script that night.  When I have time I will try the one you just wrote out for me.

                    This is what ended up working for me.

                    I created a new table to load my information into called Corporate_Accounts_Pricing_Join

                    Loop
                    Set Variable [$i; Value:$i +1]
                    Set Variable [$c; Value:$c +1]
                    Set Variable [$acct]
                    Set Variable [$class]
                    Go to Layout["Corporate_Accounts_Pricing"(Corporate_Accounts_Pricing)]
                    Exit Loop If [$i > Get(TotalRecordCount)]
                    Go to Record/Request/Page [No dialog; $i]
                    Set Variable[$SKU;Value:Corporate_Accounts_Pricing::SKU#]
                    Set Variable[$Class_SKU;Value:Corporate_Accounts_Pricing::Corp_Account_Class]
                    Go to Layout"[Corporate_Accounts"(Corporate_Accounts)]
                    Enter Find Mode[]
                    Set Field [Corporate_Accounts::Corp_Account_Class; $Class_SKU]
                    Perform Find[]
                    Loop
                    Set Variable [$acct; Value:List($acct;Corporate_Accounts::Account_Number)]
                    Set Variable [$class;Value:List($class;Corporate_Accounts::Corp_Account_Class)]
                    Go to Record/Request/Page [Next; Exit after last]
                    End Loop
                    Go to Layout["Join" (Corporate_Accounts_Pricing_Join)]
                    Loop
                    Exit Loop If [$c > ValueCount($acct)]
                    New Record/Request
                    Set Field [Corporate_Accounts_Pricing_Join::Class_SKU; $SKU]
                    Set Field [Corporate_Accounts_Pricing_Join::Account_Number_FK; GetValue($acct; $c)]
                    Set Field [Corporate_Accounts_Pricing_Join::Corp_Account_Class_FK; $Class_SKU]]
                    Set Variable[$c; Value: $c+1]
                    End Loop

                    I am sure it probably isn't as simple as yours, but it worked.  

                    At 4am that morning, that was good enough for me.

                    I would have never thought a looping script was necessary till you told me.

                    Thanks again for all your help.  I would have been trying all night to make FM do something it couldn't do without a script.