5 Replies Latest reply on Aug 6, 2014 9:53 AM by philmodjunk

    Question about relationships and reports

    bartprins

      Title

      Question about relationships and reports

      Post

           Hi there,

           Probably a noob question, but i'm justing getting started with FM again and don't seem to get it

           I've got 2 tables where there is a relation between the 2 one table is called CustomersTbl the other ProductionTbl they have a relation between the fields ClientId and ProductionId where ProductionId has the options turned on the create records and delete records.

           One client can have multiple productions, now i want to create a field for a report which sums up the amount of customers which have production records.So the customers that don't have any production don't have to be counted.

           Much oblidged for your help!!

            

           Bart

        • 1. Re: Question about relationships and reports
          philmodjunk
               

                    one table is called CustomersTbl the other ProductionTbl they have a relation between the fields ClientId and ProductionId

               I suggest that you rename Productionid to be ClientID. ProductionTbl::productionid, what you currently have, identifies the client, not the production record. This can avoid confusion in the future.

               It appears you want to get a count of all clients that have at least one related production record.

               One simple way is to perform a find on your customersTbl layout by putting an asterisk * into the ProductionTbl::clientID field and performing the find. The number of records found will be the number customers (clients) that have at least one related production record.

          • 2. Re: Question about relationships and reports
            bartprins

                 Hi philmodjunk

                 Thank you for your reply !

                 I'll rename the id's thanks for the tip.

                 The way you described is good but i want to place the outcome in a report, do you have any idea ?

                  

                 Thanks !

                 Bart

            • 3. Re: Question about relationships and reports
              philmodjunk

                   What will that report look like?

              • 4. Re: Question about relationships and reports
                bartprins

                     I would like to sum up the totals so for example:

                     10 clients - 28 production records

                     and perhaps a list with the individual clients with their records but this is less important

                • 5. Re: Question about relationships and reports
                  philmodjunk

                       I wouldn't consider a single line of text much of a "report". wink

                       As described earlier, you can perform a find for all clients that have at least one related production record.

                       Then, you can either use an unstored calculation field defined in Customers that uses Get ( FoundCount ) to return a number. (click storage options to get to where you can set this up with the "do not store" option.)

                       or you can simply use the Insert menu to insert a symbol for the found count (see other symbols) to put this text on your layout:

                       {{FoundCount}} clients

                       to get the total count of production records, define a calculation field in customers as:

                       Count ( Production::ClientID )

                       Then define a summary field in customers to compute the total of this calculation field. Then you can return to that insert menu (while in layout mode) to insert the summary field as a merge field and modify this line of text to be:

                       {{FoundCount}} clients - <<Customers::SummaryfieldNameHere>>

                       As long as you perform your find first, this line of layout text will display the values that you want.

                       With a list view layout, you can place the above line in the header and place customer name fields and the count() calculation field in the body of the layout (size the body to be just the height of your customer name and production count fields) to get these totals plus a list of the clients that have one or more production records.