1 2 3 Previous Next 70 Replies Latest reply on Jan 1, 2013 5:46 AM by brian.curran

    Counting Grandchild records for a Child to display on the Parent record

    brian.curran

      Title

      Counting Grandchild records for a Child to display on the Parent record

      Post

           I have a tricky issue and I'm unsure where or how to find a solution for it:

           We provide annual service contracts (retainers) for customers and then charge them each time we make a visit to their site. This works well for single site customers such as small businesses but the larger multi-site customers prefer an all-in approach.

           Traditionally, we have managed this all-in approach by agreeing a contract where we look after say 8 sites for a customer and pre-billing them 24 visits, a 'Bundled' deal in effect which works out at an average of 3 visits per site.

           An Excel sheet is then used to log each visit so that when the visits count down to zero, we start charging them for the 25th visit and above.

           The way our database is set up currently, if we have 8 'Sites' then they are children of a parent 'Client'. I think it might be an idea to mark this Client as 'Bundled' and then allocate an amount of visits (24).

           I then need a fancy way of adding up each visit from the associated 8 sites so that a current 'remaining balance' can be displayed on the Client layout.

           For example:
           Bloggs Co. (list of 8 child sites)
           Bundled? (checked)
           Quantity = 24
           Used = 6
           Balance = 18

           Does the logic in the above seem sound? If yes, any ideas on how I would calc' it all out?

           Thanks
           Brian.

        • 1. Re: Counting Grandchild records for a Child to display on the Parent record
          philmodjunk

               For starters, this looks like a typical invoicing situation. Whether you sell goods or (as in your case) services, this basic set of relationships can give you a starting point:

               Clients-----<Invoices-----<LineItems>-------Services

               Each visit to a site can be a line item in LineItems and Invoices can then bill a single customer for multiple visits.

               The prePaid invoice gets a bit more interesting, you might have a generic entry in line items for 24 vists and each time you vist one of the client's sites, you deduct 1 from that entry and add a new entry identifying the site visited. When your count of generic entries reaches zero, it's time to bill a new package deal.

               And scripts can be used to both create such a prepaid invoice and to update it each time you visit a site--but the details on how they would do that depend on the design of your database.

          • 2. Re: Counting Grandchild records for a Child to display on the Parent record
            brian.curran

                 Hi Phil,
                 Thanks for your thoughts. We don't intend to process invoices from FM but will (at a much later date) run 'billing reports'. For now it's just a 'log, count and check' procedure to ensure that we keep a tally on the prepaid visits (some are free actually but that shouldn't matter here)

                 I forgot to mention earlier that we also run a time frame for the tally. So Bloggs Co. might have the 24 visits valid between the 10th Dec 2012 to the 09th of Dec 2013. Any surplus remaining at the end of the period is wiped clean.

                 All customer visits will be recorded in a Table so I would need to count how many visits have been performed between two dates for each specific Site.

                 I suppose the next stage would be to count up the total visits per Site to arrive at a Client total then minus this amount from the starting amount.

                 Sounds easy, no idea where to start writing the calcs though...

                  

            • 3. Re: Counting Grandchild records for a Child to display on the Parent record
              philmodjunk

                   Even if you don't print out and deliver an invoice from FileMaker to the customer, you may want to consider that structure for managing and logging your visits. Note that it uses multiple related tables to get the job done and whether you call it an invoice or not, such an approach will be needed here.

                   What tables/relationships do you have in place to begin with?

                   Hint:

                   both relationships and find criteria can specify date ranges.

              • 4. Re: Counting Grandchild records for a Child to display on the Parent record
                brian.curran

                     Ah I see, sorry...

                     The current Tables and their relationships are:
                     Client===<Site===<Visits

                     (The above should mean one Client can have many Sites and one Site can have many Visits)

                     The Client table would hold the Start and End dates plus the total number of prepaid/free visits.
                     The Visits table would hold the _kfSiteID

                     Does that make sense?

                     Thanks
                     Brian.

                • 5. Re: Counting Grandchild records for a Child to display on the Parent record
                  philmodjunk

                       It makes sense, but I'm not convinced that putting start and End dates plus total visits in Client is the best option for you here. A separate related table for this purpose makes more sense to me. If nothing else, that allows you to see what options have been previously specified by your client.

                       Whether or not you do that, this relationship would match only to visits for the specified customer that fall within the specified date range:

                       Site::gStartDate < Visits::VisitDate AND
                       Site::gEndDate > Visits::VisiteDate AND
                       Site::__pkSiteID = Visits::_fkSiteID

                       gStartDate and gEndDate would be global fields loaded with dates from whichever table stores the start and end dates.

                       It's also possible to use a customfunction to generate a return separated list of dates for matching to VisitDate instead of two pairs of fields with inequalities.

                       And if you are using FileMaker 12, there are ways to access this same information useing the ExecuteSQL function.

                  • 6. Re: Counting Grandchild records for a Child to display on the Parent record
                    brian.curran

                         Ok great, I've created a table called 'Bundles' with Start and End date fields plus a number field to log the quantity of prepaid/free visits allocated within a specified period of time:
                         Client::__kpClientID   =   Bundles::_kfClientID (allow creation of records on the Bundles side of the relationship)

                         I have a table called 'Visits' which logs details of all visits.
                         Site::__kpSiteID   =   Visits::_kfSiteID

                         I'm guessing that the Global fields are in the Site table but I'm not sure how to load them with dates from the Bundles table. Would they be Calc fields pointing to the Bundles table, resulting in a Date calcuation?

                         I'm using FM 12 Pro Advanced but haven't looked at the ExecuteSQL function yet...

                         Thanks
                         Brian.

                    • 7. Re: Counting Grandchild records for a Child to display on the Parent record
                      brian.curran

                           I forgot to mention, it took me ages to figure out where this bit went:

                      Site::gStartDate < Visits::VisitDate AND
                      Site::gEndDate > Visits::VisiteDate AND
                      Site::__pkSiteID = Visits::_fkSiteID

                      I've added the above to the relationship between the Site and Visits tables, which I'm hoping is right. If yes, should I add a portal to the Client layout to display the results?

                           I still need to work out how to count the number of Visit records, subtract this amount off the Prepaid number to calculate a balance...

                      • 8. Re: Counting Grandchild records for a Child to display on the Parent record
                        philmodjunk

                             Note that gStartDate and gEndDate are global fields, you'll need to manually or via a script update these global fields with the appropriate dates for the current "bundles" record. OnRecordLoad may be a good script trigger to use for such a script.

                             Once you have a relationship in place, you can count the related records via one of two ways:

                             Count ( Visits::__pkVisitID )

                             can be defined in Bundles, or you can define a "count of" summary field in visits that counts __pkVisitID or some other "never empty" field and then you can refer to this summary field from the context of Bundles to see the count of how many related visits record fall in that date range for that Bundle record.

                        • 9. Re: Counting Grandchild records for a Child to display on the Parent record
                          brian.curran

                               Sorry Phil, I'm lost...

                               The Global fields are in the Site table (and on the Site layout) but they are showing up blank in Browse mode. They are set up as follows:
                               Field Name: gStartDate
                               Type: Calculation
                               gStartDate = Bundles::DateStart
                               Calculation Result is Date
                               Use global storage checkbox is Checked

                               Does the above look correct to you?

                          • 10. Re: Counting Grandchild records for a Child to display on the Parent record
                            philmodjunk

                                 Not what I had in mind and what you have won't work. You could, however, use Unstored, not global calculation fields for this.

                                 What I had in mind where straight global date fields and then a pair of Set Field steps on your bundles layout would update them each time you changed records.

                            • 11. Re: Counting Grandchild records for a Child to display on the Parent record
                              brian.curran

                                   Great stuff, the Set Field method works perfectly with an OnRecordLoad script trigger.

                                   The Client layout has the Start/End date fields and Quantity field from the Bundles table. I've also added a Count calc field to the Client table plus another Calc field to work out the balance. This is conditionally formatted so that a value of 0 or below is flagged up with a red background.

                                   Also added a portal to Visits on the Client layout, which lists all Sites and their visits. Clicking a portal row takes the user to that record in the Visits table, which will save a lot of searching later.

                                   Everything works great, very pleased with how it fits together and I even understand it now that it's built ;) I only have one snag left to work out, I've just realised that some Sites don't have a parent Client as they are single site locations!

                                   Thanks very much Phil, I really appreciate your help and patience...

                              • 12. Re: Counting Grandchild records for a Child to display on the Parent record
                                philmodjunk

                                     I've just realised that some Sites don't have a parent Client as they are single site locations!

                                     Just create a Client record from the data in the site record. This can be done with a script.

                                • 13. Re: Counting Grandchild records for a Child to display on the Parent record
                                  brian.curran

                                       Stuck again I'm afraid...

                                       To account for Sites that don't have a parent Client, I added a _kfSiteID to the Bundles table so that bundled deals can be applied to either a Client with several child sites or directly to a single Site. The Visits table hasn't changed, as visits only apply to Sites anyway.

                                       I then had to make a duplicate TO of Bundles so now I have:
                                       Client::__kpClientID   =   BundlesClient::_kfClientID 
                                       Site::__kpSiteID   =   BundlesSite::_kfSiteID 

                                       I was feeling pretty confident until I realised none of it works now...

                                  • 14. Re: Counting Grandchild records for a Child to display on the Parent record
                                    brian.curran

                                         Ok, I got it back to where I have the Client layout working fine with all the Visits and calculations etc.

                                         The Site layout isn't bringing up the Count calc in the Sites table which is "Count ( Incidents::__kpIncidentID )" I'm wondering whether I need to duplicate the Visits TO as well?

                                    1 2 3 Previous Next