4 Replies Latest reply on Nov 8, 2011 6:08 PM by RafaCorrea

    Calculation from portal records



      Calculation from portal records


      Hi, I am new with DB in general and I’m learning on the go. I created a research database and I am having problems doing some calculations with some portal records, let me explain:

      In Table one each records is for a specific clinical encounter, and it has a portal which has on each record information on a follow up visit (date, time since encounter, complications, severity of complications and attributability of complication to encounter).

      Now most of the portal records don’t have a severity or attributability field filled.

      One of my goals is to be able to count how many of the encounters have an attributability  related portal record that is different that “Unrelated”

      One of my goals that I have not been successful at is to be able to select encounters that have any visit (Not just the first one that is one of the problems I have encountered) with an attributability field filled with something different to “Unrelated” so I can use that result in a ratio calculation with another field.




        • 1. Re: Calculation from portal records

          does the attributability always have some text in it, either "unrelated" or some specific entry?

          And you want a count of all records in the portal where "unrelated" is the value in this field?

          If so, do you have FileMaker 11? (this is much easier to do in version 11 than in older versions, but it can be done in older releases also.)

          • 2. Re: Calculation from portal records

            Thanks for answering

            The Attributability field only has text in it (either "unrelated", "possibly related", "attributable" etc...) when that portal record represents a follow up visit with a complication, for the follow up visits with no complications, it is empty.

            I want to be able to count encounters where all the portal associated records (folllow up visits) either have no complications or are unrelated.

            I am using FM 10, but I could look into 11 if it is easier.

            Thanks again

            • 3. Re: Calculation from portal records

              Here's the Filemaker 11 solution:

              Define a summary field in the portal's table as the count of some field that is never empty.

              Now add a portal to your layout with the same portal setup options as your current portal, but make it a single row portal and specify this portal filter: (Requires Filemaker 11) FollowUps::attributability = "unRelated" OR IsEmpty ( FollowUps::attributability )

              (Use your name for the portal's table occurrence in place of "FollowUps".)

              Put your summary field inside this single row portal.

              With FileMaker 10, you'll need to put in some more work here.

              Define this calculation field, cNoComplications, in the FollowUps table:

              attributability = "unRelated" OR IsEmpty ( attributability )

              Select Number as the return type and clear the "do not evaluate if all referenced fields are empty" check box. This gives us a field that will show a 1 in it if attributability = "Unrelated" or is empty.

              Now define a calculation field, cNoCompsFlag in your parent table as the single value: 1 or you can enter True to get the same result.

              Now a relationship can be added to your database:

              Patients::PatientID = FollowUpsNoComps::PatientID AND
              Patients::cNoCompsFlag = FollowUpsNocomps::cNocomplications

              And now you put your summary field directly on your layout to show the total count. Or you can define a calculaiton field in Patients as Count ( FollowupsNoComps::PatientID ) to show the same value. (This calcualiton field won't work with a filtered portal.)

              • 4. Re: Calculation from portal records

                Thanks.. I will try to get FM 11 and and give it a try.