12 Replies Latest reply on Feb 8, 2015 9:39 AM by tays01s

    Summing amounts by 2 different routes

    tays01s

      Title

      Summing amounts by 2 different routes

      Post

      Input::__ID < DrugLIst::_IOID

      Amongst the DrugLIst are 'enteral' and 'parenteral' drugs. I'd like an Input::Sum_enteral and Input::Sum_parenteral fields. Can I do this through a calculation and a single relationship and TO?

        • 1. Re: Summing amounts by 2 different routes
          philmodjunk

          Option 1:

          Use ExecuteSQL() in a calculation field to compute one or both subtotals

          Option 2:

          Define two calulation fields in DrugList:

          If ( Type = "Enteral" ; FieldToSumGoesHere )

          If ( Type = "Parenteral" ; FieldToSumGoesHere )

          Then, in Input, compute the sum of these two separate fields.

          Option 3:

          Define a summary field in DrugList that sums the field in DrugList that you want to sum. Set up two single row filtered portals to DrugList and put this summary field into both of them. The Portal filters will use the same logic found in the IF functions shown for Option 2. Example:

          DrugList::Type = "Enteral"

          Option 4 (doesn't meet your pre-conditions)

          Go ahead and set up the needed extra table occurrences and use a relationship to selectively sum the records you want to sum!

          • 2. Re: Summing amounts by 2 different routes
            tays01s

            SQL: I'm new to this, so I've not managed to spot what's wrong with the Calc field I created with the syntax:

            ExecuteSQL("SELECT SUM (amount) FROM FeedsUsed2 WHERE Route='G' or 'I' FROM FeedsUsed2";"";"")

            where:

            - 'amount' is the field I want summed.

            - 'Route' is the field by which I want to filter the summing and both these fields are in the FeedUsed2 TO.

            • 3. Re: Summing amounts by 2 different routes
              philmodjunk

              Route = 'G' or Route = 'I' and remove the second FROM clause.

              Also, it's often necessary to enclose the field names inside double quotes to keep SQL from confusing field or table names with SQL key words. I think you're OK here, but if you still get ? results, you might try enclosing the  field names in quotes like this:

              "SELECT sum ( \"amount\" ) FROM...

              • 4. Re: Summing amounts by 2 different routes
                tays01s

                1. Apparently my mistake was using the TO 'FeedsUsed2', instead of just FeedsUsed'. However, because I need the SUM of FeedsUsed to be only from a particular IO record, do I add another: WHERE

                & do I use a tableX.field = tableY.field syntax..........& where do I put this WHERE.

                2. So \"field\" is the syntax for denoting a field (where it may overlap with SQL syntax)? Not just " "

                • 5. Re: Summing amounts by 2 different routes
                  philmodjunk

                  1) I have no idea as I cannot follow your description nor see why you need more than one WHERE clause.

                  2) You have to be able to get the double quotes inside a string of quoted text. If you used " ", FileMaker would interpret that as the end of the quoted string and throw an error when you tried to close the specify calculation dialog. The \ character "escapes" the double quote and fileMaker then treats it as another character inside the quoted string instead of the character marking the end of the quoted string.

                  • 6. Re: Summing amounts by 2 different routes
                    tays01s

                    1. Sorry, me being unclear.

                    ExecuteSQL("SELECT SUM (amount) FROM FeedsUsed WHERE Route='G' or Route='I'  ";"";"")

                    the WHERE has correctly dealt with Route. However, the SUM is summing all FeedsUsed. What I need is only those related to the Input record from FeedsUsed parent. I wondered how, in SQL you write syntax to filter for a field in a different table.

                    • 7. Re: Summing amounts by 2 different routes
                      philmodjunk

                      You can use a join clause to specify a relationship, but you can also just include that term in your existing WHERE clause:

                      ExecuteSQL("SELECT SUM (amount) FROM FeedsUsed WHERE ( Route='G' or Route='I' ) AND ParentID = ?  ";"";"" ; Parent::ParentID)

                      This assumes that the match field in FeedsUsed is named ParentID

                      • 8. Re: Summing amounts by 2 different routes
                        tays01s

                        Have tried: ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE (Route='G' or Route= 'I') AND _IOID=IO_n";"";"";Patient::IO_n)

                        but results = ?

                        I haven ensured it's not simply an overlong decimal in the field box (caught me out before). I also have other fields showing that there really are values that should be shown.

                        • 9. Re: Summing amounts by 2 different routes
                          philmodjunk

                          Why did you not follow the example posted? That question mark in my example has to be there for this to work.

                          ExecuteSQL("SELECT SUM (amount) FROM FeedsUsed WHERE ( Route='G' or Route='I' ) AND ParentID = ?  ";"";"" ; Parent::ParentID)

                          ExecuteSQL will replace the ? with the value of Parent::parentID when it executes this query.

                          • 10. Re: Summing amounts by 2 different routes
                            tays01s

                            My syntax ignorance is showing !! Unfortunately I still get a '?' having tried your syntax, but I wonder if it is because of the relationship setup I've used.

                            I should mention that the fields all appear on a Patient layout and that relationship hierarchy is: Patient < Calc < IO < FeedsUsed. Fields from the latter 3 tables are shown in portals with TOs 'IO 2' and 'FeedsUsed 2' being used to call values in the correct context. When an 'upstream' record is activated, it sets the downstream field using a Patient::upstream_n = Downstream::_upstreamFKID relationship.

                            I don't know whether the above is relevant re. SQL, but should the calculation field in IO, summing FeedsUsed, actually be calculated from the 'IO 2' context?

                            Also I'm not sure whether I should be using: Patient::IO_n or IO::__ID. I'd wondered whether your ParentID = ? really meant _IOID and Parent::ID meant IO::__ID or Patient::IO_n according to my relationship setup. However, I've tried all combinations and failed.

                            • 11. Re: Summing amounts by 2 different routes
                              philmodjunk

                              A ? results means either you have a syntax error or the field is too small to fully display the value of the field. Best guess is that you still have a syntax error here.

                              I suggest copy/pasting the exact calculation that you have set up.

                              Note that my example expression assumes two tables named Parent----<Child and thus the ? refers to the value of a field in the Parent record not the child. I am also assuming that you have defined this as a calculation field defined in the parent table. If you have more than one occurrence of this table, be careful to select the correct occurrence from the "context" drop down when you define this calculation.

                              • 12. Re: Summing amounts by 2 different routes
                                tays01s

                                My current equation is:

                                ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed 2 WHERE (Route='G' or Route= 'I') AND __ID=?";"";"";IO 2::__ID)

                                'IO 2' & 'FeedsUsed 2' are the portals showing the fields on the Patient layout.

                                As mentioned the primary relationships between tables are: Patient < Calc < IO < FeedsUsed.

                                Fields from the latter 3 tables are shown in portals with TOs 'IO 2' and 'FeedsUsed 2' being used to call values in the correct context. When an 'upstream' record is activated, it sets the downstream field using a Patient::upstream_n = Downstream::_upstreamFKID relationship.

                                I've made sure the calc field in the IO parent table is wide enough; it definitely works fine if I don't try to introduce the relationship using 'AND'.