1 2 Previous Next 28 Replies Latest reply on Feb 4, 2014 7:11 AM by MallieWilliams

    Calculation Issue

    MallieWilliams

      Hello everyone,

       

      I have a field that counts down the tickets left. Which is capacity - s_SumTicketsSold? It works until I add a new record, then it gives me the opposite count of the calculation. (Example) Say I had 15 tickets left. If I add new record it will show me that I have -109 left. Ex_1 shows the correct calculation and Ex_2 shows when I add new record. Any ideas?

        • 1. Re: Calculation Issue
          MallieWilliams

          Attached is the screenshot of the tables and fields.

          • 2. Re: Calculation Issue
            taylorsharpe

            You might try refreshing the portal.  If you have a create new record script, include the refresh of that object at the end of the script.  Or you can refresh the whole page, but refreshing just an object is faster. 

            • 3. Re: Calculation Issue
              Mike_Mitchell

              Two things that jump out:

               

              1) I don't know what the auto-enter calculations are in your capacity fields, but they may not be evaluating correctly.

               

              2) Your summary fields will evaluate across the entire found set, not just the current record. Hence, they're likely bigger than you're expecting.

               

              I'd say the most likely candidate for the problem is the summary fields. Not sure what you're really wanting, but if you want to know how many tickets are left for a given event, then you'll need to set up a self-join to Event and use Sum so the system can calculate how many tickets have been sold for each event (and no, a Cartesian join won't do it).

               

              Mike

              • 4. Re: Calculation Issue
                MallieWilliams

                Thanks Taylor,

                 

                I've tried that. Still no luck..

                • 5. Re: Calculation Issue
                  MallieWilliams

                  Hey...Mike,

                   

                  So do I get rid of the Cartesian join and just make a join between Events and TicketSales? Did you get a chance to look at the Untitled image about that shows my table layout?

                  • 6. Re: Calculation Issue
                    Mike_Mitchell

                    You need a join between Events and itself, but not based on a Cartesian. The Cartesian joins every record to every other record, so it's impossible for the calculation to know which event it's summing up. The relationship needs to join each event only to itself. That way, you can sum up only those sales that relate to that particular event.

                     

                    As far as deleting the Cartesian, I can't answer that question. I don't know what else it might be used for in the solution.

                    • 7. Re: Calculation Issue
                      MallieWilliams

                      Hey..Mike I'm sorry I'm still learning about building databases.. Are you saying that I need to make two extra table occurrences based off of the Events table and use one of them as a join table? Attached is the example file that I have. In the Tickets tab in record 1 The capacity for F and S are set for 124. When I add 5 into F tickets the F tickets left takes away which gives a count of 119 this works good for the first record. When I go to the second record it doesn't work. I want the Tickets Left for F and S to show on every record so that the countdown of tickets can be shown.

                      • 8. Re: Calculation Issue
                        Mike_Mitchell

                        Your data model is ... confused. You have the contact listed on a layout for the Events table. But that's only going to show the first related contact for the event, not the event itself. Your portal is pointing to the join table between Events and Contacts, but you're listing fields from the current context (Events) in that portal. This is why they don't refresh properly.

                         

                        If you want to have each record represent a contact, then the layout needs to be based not on an event, but on a contact. You will also need to do away with the summary fields (these are largely used for reporting, not for doing the kind of totalling you're attempting) and use a calculation based on Sum instead.

                         

                        As a thought exercise, print off a copy of your Relationships Graph. Pretend you're standing on the Events table occurrence (TO). Then look into the Contacts TO and envision what contact you'll see. Look into the portal for the TicketSales TO, then back into Events for the capacity_count fields. This is what FileMaker is doing with your current setup.

                         

                        I think you need to grab a copy of the FileMaker Training Series and go through Chapter 3 (Data Modeling) very slowly and very deliberately. You need to understand the concept of context before you proceed any further, and you need to get the fields into the right tables.

                         

                        HTH

                         

                        Mike

                        1 of 1 people found this helpful
                        • 9. Re: Calculation Issue
                          erolst

                          Mike_Mitchell wrote:

                          I think you need to grab a copy of the FileMaker Training Series and go through Chapter 3 (Data Modeling) very slowly and very deliberately. You need to understand the concept of context before you proceed any further, and you need to get the fields into the right tables.

                           

                          … and maybe also read (and follow!) the advice you received earlier re the design of your solution, e.g. in this thread.

                          • 10. Re: Calculation Issue
                            MallieWilliams

                            Your right Mike. I have the training series. I need to go back and go through it slowly. Thanks for your help and guidance. The hardest thing about self-training is if I have questions there is no one that I can reach out to and talk to.

                            • 11. Re: Calculation Issue
                              Mike_Mitchell

                              It's okay.

                               

                              Just remember: In FileMaker, context is king. Every layout is based on a TO. That determines its perspective on the Relationships Graph, which, in turn, determines the perspective for every calculation and layout object.

                               

                              Think of it as a series of stepping stones and pipes. You stand on the "stone" represented by the current layout, and look through the pipes that connect it to the other stones. This will give you the idea of what the layout sees.

                               

                              When first learning, it's often useful to print out a copy of the graph so you can refer to it as you work, thinking, "Where am I at the current moment?"

                               

                              Good luck.

                               

                              Mike

                              • 12. Re: Calculation Issue
                                MallieWilliams

                                Thanks Mike.. I really appreciate your positive response and help. Thanks again!!

                                • 13. Re: Calculation Issue
                                  MallieWilliams

                                  Hey Mike,

                                   

                                  I made the changes to the file as far as the layout. The issue I have in tickets is in the portal. I have my capacity_F - (TicketSales::qtyBought_F) which gives the capacity count_F field the total. The problem is that total needs to count down and show on the next record and then the next and so on if that makes sense. It seems that I need to write a script in order for this to take place. Or do I need to do a different calculation on that field to make it count down the capacity?

                                  • 14. Re: Calculation Issue
                                    Mike_Mitchell

                                    Your fields are in the wrong tables. You need to back up and start from scratch on where the fields belong in what tables.

                                     

                                    An event has a number of tickets to sell. This is its capacity. (You can simplify as you have, using a capacity for Friday, Saturday, and so forth, although that's not strictly the correct way to do it, but it will work for now.) Therefore, the "capacity" fields belong in the Events table, not in TicketSales (which tell you what person has bought which tickets). (As an aside, you could just use the capacity from the venue, but there may be a need to adjust it if the venue can be modified based on the type of event. Many can.)

                                     

                                    Each ticket sale is recorded in TicketSales. This is qtyBought. That's correct. So if I want to know the number of tickets remaining, I need to subtract the total number of qtyBought for each event from the capacity for that event. Hence, my remaining capacity is:

                                     

                                         capacity - Sum ( TicketSales::qtyBought )

                                     

                                    That's it. NOTHING MORE. You do not need a lot of what you have:

                                     

                                    - Capacity fields in TicketSales

                                    - Summaries of tickets bought (cSumTicketsSold), unless you have a report somewhere that reports on them

                                     

                                    You don't need to put the remaining capacity in the TicketSales table in order to show it in the portal. You can just put the field from the Events table in the portal, and it will be correct. The fact that you're moving from contact to contact is irrelevant; the total number of tickets sold versus the number available has nothing to do with what person you're looking at.

                                     

                                    Hope that makes sense.

                                     

                                    Mike

                                    1 2 Previous Next