6 Replies Latest reply on Mar 22, 2014 12:04 PM by ffdstudios

    Selecting portal records to add into invoice total

    ffdstudios

      Title

      Selecting portal records to add into invoice total

      Post

           I am using Filemaker Pro 12 to create a database for a Dog Walking/Pet Sitting business.  Tables are as follows:

           Customer: Parent Table to hold basic client information such as name, address, email, phone, etc

           Pets: Child Table to hold basic pet information such as name, breed, allergies, etc

           Events: Child Table to hold calendar information specific to when a client wants their pet walked/looked after, such as date, time, type of event (pulled from the Scheduling table), event total, which pets will be walked/looked after, etc

           Scheduling: Parent Table which holds the info pertaining to event types such as Session Type, First Pet cost, Additional Pet Cost, Holiday Cost.

           Pets_Events: TO from the Pets table to link to the events table, so when the  a new event is created and the client is chosen, a list of all their pets is shown on the event

      Table Relationships

           Customer::_pkCustomerID = Pets::_fkCustomerID

           Customer::_pkCustomerID = Events:_fkCustomerID

           Scheduling::_pkSchedulingID = Events::_fkSchedulingID

           Pets_Events::_fkCustomerID = Events::CustomerName

      ISSUE

           I have a portal on the Events table layout so when I create a  new event and choose the customer, all of their pets pop up.  Sometimes, however, a customer doesn't want all of their pets looked after during one particular event.  If the customer has a dog and a cat, but only wants to schedule a 15 minute walk for their dog, I don't want to charge them for their pet.  The way I have the calculation set up now, the event total takes into account all animals the customer has.  I would like to (perhaps use a radio button in the portal) and pick which animals the customer wants looked after, and then charge accordingly.  I realize I cannot put a radio button on the Pets table and then pull that into the portal, because I want it to be based on the event, not the pet and that would change other invoices for other sessions.  I tried creating a radio button on the Events table and put that into the portal, but when I chose 1 pet, it selected all pets.  Then I tried making it global, but the same thing happened.  So I'm at a loss....  any and all help is appreciated!  

      Screen_Shot_2014-03-21_at_11.47.37_AM.png

        • 1. Re: Selecting portal records to add into invoice total
          ffdstudios

               Here's the shot of the layout

          • 2. Re: Selecting portal records to add into invoice total
            philmodjunk

                 I have a portal on the Events table layout

                 And this is a portal to what table? Pets? or Pet_Events?

                 A portal to pets should list all Pets for a selected customer. A Portal to Pet_Events should only list the pets involved in that specific event. That makes your events table very much an "invoice" table and the Pet_events table becomes your "LineItems". But this requires that Pet_Events be a different table from Pets and not a second table occurrence of pets--which appears to be the case from what I see in your relationships screen shot.

                 The relationships should be modied to be:

                 Pets>-----Customer-------<Events------<Pet_Events>----Pets 2

                 Pets::_fkCustomeriD = Customer::_pkCustomerID
                 Customer::_pkCustomerID = Events::_fkCustomerID
            Events::_pkEventID = Pet_Events::_fkEventID
                 Pet_Events::_fkPetID = Pets 2::_pkPetID

                 You can set up a portal to Pet_Events and format _fkPetID in the portal row as a drop down list of only the Pets for the customer selected for that event. That's the simplest to set up. With scripting, you could set up a portal to Pets with buttons that you can click that perform a script to create the needed record in Pet_Events with conditional formatting used to show which pets were selected for that event.

            • 3. Re: Selecting portal records to add into invoice total
              ffdstudios

                   Thanks Phil for your quick response.  I am still getting tangled up on this however.  I completely understand your explanation however the execution on my part is just not there...  I've created the Pet_Events table with fields _pkPet_Events, _fkEventsID, and _fkPetID.  I linked that to the Events table as you stated above in red.  I also created another TO of Pets (Pets 2) and linked as you also stated above.  This all makes perfect sense in my brain, however I think my frustrations are clouding my ability to think this through....

                   How is Pet_Events going to get populated to make the invoice lines?  I am picturing using the layout I created based on the Events table, where I pick the customer, the date and time and then add a button to 'add a pet'?  Click the button and it takes me to the Pet_Events table based layout where I can choose from the pets that are associated with this customer, and then have that selection be put back to the Events layout, and maybe the option to add another pet?  I guess I'm just not grasping how the Pet_Events table is being populated, to create the information needed for the Events layout.  

                   Also, when I put the portal on the Events tabled based layout to Pet_Events and formatted _fkPetID as a drop down list with only pets, it will not populate on this layout, which leads me to believe I have issues with my relationships - I can populate the drop down list with all pets names in the database (if I am on the Pet_Events table layout), however it won't filter when placed on my Events table based layout...

              • 4. Re: Selecting portal records to add into invoice total
                philmodjunk
                     

                          How is Pet_Events going to get populated to make the invoice lines?

                     By adding a portal to Pet_Events on the events layout. If you select "allow creation of records via this relationship" for Pet_Events in the events to Pet_events relationship, you'll be able to select Pets via the drop down list or pop up menu that I recommended for the _fkPetID field in Pet_Events. Take a look at one of the invoices starter solutions that come with FileMaker 11, 12 or 13. Note how a portal to InvoiceData on the Invoices layout is used to select items to be sold. Your Invoice is called Events and you are selling a service to your customer for each of their pets that you select in the portal to Pet_Events.

                     

                          when I put the portal on the Events tabled based layout to Pet_Events and formatted _fkPetID as a drop down list with only pets, it will not populate on this layout,

                     This is not a relationships issue. A value list set up with the "use values from a field" value list will not be affected by relationships unless you have also selected the "include only related values" option. Try this first without that option specified. This will produce a value list of all pets from all owners and thus isn't the final version to use, but try that to be sure that you have the basics working.

                     Once that works, we can take a stab at making this a conditional value list that only lists pets for the selected customer.

                • 5. Re: Selecting portal records to add into invoice total
                  ffdstudios

                   If you select "allow creation of records via this relationship" for Pet_Events in the events to Pet_events relationship, you'll be able to select Pets That is what I missed.  Now the portal is working.  

                  Once that works, we can take a stab at making this a conditional value list that only lists pets for the selected customer.

                       Ok the drop down/pop up list is working and shows all pets and I'm ready to continue :)

                        

                  • 6. Re: Selecting portal records to add into invoice total
                    ffdstudios

                         Well I've been playing around with it and figured it out (obviously with your help, Phil, a huge thanks to you!).  I was going through my Events layout with a fine toothed comb (and after some sleep to clear the frustrations out of my head) and realized that I wasn't using the _fkCustomerID field on the Events table to populate the customer name, which was resulting in the portal to not filter correctly.  Once I found that mistake, I fixed it and now my portal pet name popup field only shows the proper names associated with the customer chosen on the Events layout.  YAY!!  Time to move on to playing around with it some more to use the selections in the portal (line items) as a way to total the amount owed for the event.  Now that I'm on the right track again, I should be able to go from here.  If not....I'm sure I'll be back with more questions.  

                          

                         Thanks again Phil!