1 2 Previous Next 17 Replies Latest reply on May 8, 2012 7:50 AM by johnhorner

    Displaying count for multiple related factors

    JoeMintz

      Title

      Displaying count for multiple related factors

      Post

      Right now I have my Main Layout/Table set up.  I also have a related table which is set up to create status' and to count the number of each status.  That is working great, I am having a tough time when I try to add in another factor to count.

      I created a calculation on the Main Table that calculates a NUMBER from the last time a user wrote a note on a file.  So it takes current date and subtracts user time stamp.

      What I am trying to do now is create a count across all records that counts for example Status 1 and Number > 2 days, or Status 2 and Number > 5 Days.

      I got as far as creating another related table, and creating 20 records, and creating a count for each possible "number" but I feel like I am going at this completely wrong.

      I can't seem to link together the right pieces.  Can anyone help?

        • 1. Re: Displaying count for multiple related factors
          johnhorner

          it would help to know if there is a maximum number of different "ages" you would have to track and, if so, what that maximum is.  i am assuming it is not a fixed number (meaning there be ages for 1 day, 2 days, 3 days,..., etc for years?)

          a couple quick thoughts, depending on how you want to display this information...

          you could make a self join using the relationship "age = age" and use a simple calculation field to get the count (using the "count" function) of the related records.  you could put this field into each portal row (assuming that you are looping at the records in a portal).  that, of course has lots of drawbacks.  for example, if you have lots of records you would have to scoll all the way through to see all the possible subtotals.  but if there are only a handful of related records in your portal, it's not quite so cumbersome and ineffective.

          if it is something you only need "on demand", perhaps a better method would be to make a report sorted by "age" with a sub summary part containing a summary field which would be the "count" of the id field, or "age" field, for example, for the records in question.

          another option would be a script to calculate the totals (more along the lines of a report to be generated only on occasion).  you could go to an empty layout for the main table, sort the records by age, and then start going through them one at a time (loop with go to record next).  a variable would track the "age" and the umber of occurrences of that particular age until it hit a new one.  at that point the "age" and the number of occurrences could be written to a "master" variable (or a global field for this purpose), at which point the age and count variables would be reset and start counting the number of occurrences of the next age... and so on until the last record.

          i'm sure there are other (better?) methods out there but depending on your situation i think any of these could work... good luck!

          • 2. Re: Displaying count for multiple related factors
            JoeMintz

            I am trying to make it so that I can put a button, that will display red, when there exists a "Status" with Age > 2.  This would need to be in real time.  I would use the conditional for the button, but I can't figure out how to combine the two.  Or even if I had to put them in the portal would be fine too.

            • 3. Re: Displaying count for multiple related factors
              johnhorner

              hey joe... i don't fully understand what you are trying to achieve. what do you mean "combine the 2?  status and age?  i have been assuming that they are 2 separate fields but i suppose status could be your field and age (or number) could be the contents of the field...? please let me know which so i can better try to help.

              you can have more than one condition for your relationship such as status = 1 AND age > 2 (the numbers "1" and "2" here would have to be stored in a global field, of course, in order to be used in defining the relationship... it would actually be nice, come to think of it if filemaker would let you insert constants when defining relationships... but for now anyway, you need to make fields).  when defining the relationship you can add additional field pairs (i have done as many as 3 or 4 field pairs but i think you can have many more than that... i don't actually know the maximum number?).

              if you only want to indicate when there exist any record with status with age greater than 2, you could make a relationship with that as the criteria (again, you would have to store the number "2" in a global field to use it in defining the relationship), and then all you would need is conditional formatting (if count of related records in this relationship > 0 then... color is red, or orange, etc).  you could use this outside the portal to indicate if any record in your target set (all records, or just records of status x... defined as part of the relationship) had an age greater than 2, or you could use (with conditional formatting) to highlight any portal rows with an age greater than 2 (if age > 2, then color red, etc).  is this what you mean?  if not, please explain more and i will do my best to help.

              • 4. Re: Displaying count for multiple related factors
                JoeMintz

                Here is a picture of what I have going on.

                There is the portal from Status Tracking::status.  This is a related table.  I actually set it up so that when I click on any of the status' it searches them, which is pretty cool.

                The second item outlined in red is in my main database and is a calculation.  It is Get(CurrentDate) - Last Notes Date, and displays a number (which you refer to as "Age" so I will also).  This is not indexed, it is an unstored value.

                I am trying to figure out the best way to make that "Follow Up Alert" button light up (using conditional for the button) if for if there are any records that meet the following condition (I guess a 'Count' of the the records that meet the following condition):

                1) In Progress - Waiting for Contract (Digital) AND 2) Age > 2

                I tried some different methods, but had a lot of trouble.  I'm looking for the best method to do exactly what I explained in here.  How would I get a count of that condition?

                • 5. Re: Displaying count for multiple related factors
                  johnhorner

                  ok... this is good... i am understanding a little better.  a few questions:

                  i can see that your portal is based on the TO "Status Tracking".  it appears that the portal is on a layout based on your "Main" table (is this correct?).  i am also assuming that the relationship you have between your main table and the "Status Tracking" TO is defined to show all records using the "x" operator (is this correct)?

                  if i understand the set up and what you are trying to do, i think you can accomplish it as follows:

                  1. create a global text field in the main table called "STATUS" (or whatever name would conform to your naming convention for global fields).

                  2. set the value in this field to "In Progress - Waiting for Contract (Digital)".

                  3. create another global number field in the main table called "TWO" (or something similar along the lines of your naming convention) and set the value to "2".

                  4. create a TO based on the Status Tracking table called "maintable_STATUSTRACKING_FollowUp" (or equivalent) and use the following 2 conditions to define the relationship:

                  STATUS = Status Tracking::status (assuming that "status" is generated from a calculation or input from a value list (or validated to be from a list of values), otherwise you run the risk with such a long name that there will be a typo and it will not match the global field... if not i would implement one of these input strategies/validation)

                  TWO ≤ Status Tracking::age

                  (for this to work you will need to do one of at least two things: change the storage option for the age field by unchecking "do not store result", which may cause issues with how reliably it will update... you could use a few script steps to run on file open that would take care of this.  or, an alternate method would be to make a second field ("age_noncalc" or equivalent) that is a number field with an auto-enter calculation (replace existing value option) and set it equal to "age".  then you could use this new "age_noncalc" field in the relationship and it should update and stay in sync with your original, non-stored "age" calculation field.  again there are probably other (perhaps better) ways to achieve this too.  if so, hopefully others will recommend).

                  5. set up conditional formatting on the button (i don't think you can actually apply conditional formatting strictly speaking to a button but if your button is a field being used as a button, such as a container field with a button graphic for example, it will work, otherwise you can use a text object (with just a space in it) overlayed on top of the actual button, or just use the graphic button for visual purposes and make the text object the button itself... and probably several other options).  anyway the formatting would be:

                  formula is: count ( maintable_STATUSTRACKING_FollowUp::age ) > 0 (i used the "age" field here but in practice i generally use the ID field for the record when generating counts... it is less likely to return an inaccurate count but in this case you would always get the same (and reliable) result).

                  another thing you could do to indicate/display records that need follow up, is to make another portal that only displays these "follow up needed" items.  you would use the same new TO created above, meaning that you would show related records from "maintable_STATUSTRACKING_FollowUp" (instead of from the "Status Tracking" TO).  you could put that on a tab panel so that you could have tabs to show the portal displaying all records, vs the portal showing "follow up needed" records, etc.  you can use conditional formatting to display an indicator if there are records in the "follow up needed" portal.  i sometimes will use a simple period (".") at a very large point size which i set the font color to red if my condition is met, or else the background color (so it becomes "invisible" otherwise).  alternately, i use a calculation field with a container result to display a graphic.

                  i hope this is helpful!

                  • 6. Re: Displaying count for multiple related factors
                    JoeMintz

                    Can you de-abbreviate "TO" I don't know what you're referring to and it's mentioned a lot.  Is that just a table or a layout?

                    Also, here is a picture of what my database looks like before attempting this.

                    • 7. Re: Displaying count for multiple related factors
                      philmodjunk

                      TO = Table occurrence. Each box in your screen shot is a TO.

                      To learn more about them, see this thread: Tutorial: What are Table Occurrences?

                      • 8. Re: Displaying count for multiple related factors
                        johnhorner

                        sorry about that.. "TO" stands for "Table Occurrence".  the picture of your database shows 4 TO's for example (escheat, last notes, status tracking, and escheat 2).  in general, although you can name it however you like, a table occurrence typically contains the name/abbreviation of the table on which it is based along with other identifying information.  this makes it a little easier to understand the structure.  there are also different ways to structure the "relationship graph" (which is what is depicted in your picture).

                        this has nothing to do with your particular question, but it appears to me that you have created a special field ("anyfield") for the relationship between escheat and status tracking.  i don't believe there is anything wrong with this other than additional clutter, but this is not necessary to show all the records from status tracking in a portal on layouts based on the escheat table.  you can use any selectable field (such as "Relative 3" and "status") and as long as you use the "x" operator (as you are doing), and it will do the same thing.

                        let me know if you have any more questions...

                        • 9. Re: Displaying count for multiple related factors
                          JoeMintz

                          STATUS = Status Tracking::status (assuming that "status" is generated from a calculation or input from a value list (or validated to be from a list of values), otherwise you run the risk with such a long name that there will be a typo and it will not match the global field... if not i would implement one of these input strategies/validation)

                          TWO ≤ Status Tracking::age

                          So basically, STATUS = maintable_STATUSTRACKING_FollowUp::status then, since that's the occurrence?

                          And TWO  maintable_STATUSTRACKING_FollowUp::age?

                          Problem is, the age calculation is in the Escheat table.

                          • 10. Re: Displaying count for multiple related factors
                            JoeMintz

                            Yeah so looks like I can set the conditional just fine using 

                            count ( Escheat 3::age ) > 0

                            But still can't seem to get both conditions involved.

                            • 11. Re: Displaying count for multiple related factors
                              johnhorner

                              essentially.  in the expression: "STATUS = maintable_STATUSTRACKING_FollowUp::status", it breaks down as follows

                              "STATUS" is the name of the global field which contains (permanently and for all records) the text "In Progress - Waiting for Contract (Digital)" which you would copy and paste into it (rather than type... for accuracy's sake).  it is not proceeded by the name of a table occurrence because it is from the "current" table occurrence.  this is something you specify in the "specify calculation" window and most of the time the context for your calculation is the table on which the layout is based.  or in the case of portals, it is the TO on which the portal is based.

                              "maintable_STATUSTRACKING-FollowUp" is the name of the TO showing records from the table "Staus Tracking" (at least, i assume that is the name of it).  i am most familiar with a "structure" or "layou" of the relationship graph called "anchor-bouy".  so in my naming convention i start with lower case letters and use the name of the "parent" table on the left side of the relationship.  i then use all caps to show the name of the table on which the TO in question is based.  finally i add some explanatory text so i know what this TO is for.  so, for example, in your relationship graph, if i were to use my naming convention i would call the TO you currenty call "Status Tracking" something like "escheat_STATUSTRACKING_All" and i would call "Escheat 2" somethign like "escheat_statustracking_all_ESCHEAT_InProgress".  it is not a big deal when you only have 4 TO's but once you start to get a more complicated relationship graph with dozens or hundreds of TO's it is essential to be able to manage it more carefully.

                              back to your problem.  i probably should have asked more questions about how you are using this and what you need it to do because there may be a better solution.  if you want t take the time to explain the whole thing in more elaborate detail, i would be happy to offer my thoughts.  it may be that your "age" field should be in the status tracking table.  in general, it is best to place fields that describe an attribute (e.g. "age") of an entity ((e.g. "status indicator") in the "entitiy's" own table.  or it may be that you don't need the status tracking table at all.  it depends on many things.

                              so you have a few options.  you could make a slightly more complicated relationship graph and go from escheat to statustracking_followup to another occurrence of escheat where the "age" field in escheat is greater than a global "TWO" field in status tracking.  or, you could just implement the age field in the status tracking table and go with what i had described previously.  another thought i had (there are always a handful of ways to do everything in filemaker), would be to create a date field in status tracking with an auto-enter calculation that would insert the current date plus 2 days.  and then define your relationship such that "age" in escheat > "ageplustwo" in status tracking.  similar result, slightly different way of looking at it.

                              i will try to make a quick sample file for you in the next couple days as that may be the best way to show you.  but please let me know if you are able to get it working before then.

                              • 12. Re: Displaying count for multiple related factors
                                johnhorner

                                when you say you can't seem to get both conditions involved, pelase explain in more detail what you mean and what is happening that prevents you from doing so.

                                • 13. Re: Displaying count for multiple related factors
                                  JoeMintz

                                  Alright I did this:

                                   

                                  you could make a slightly more complicated relationship graph and go from escheat to statustracking_followup to another occurrence of escheat where the "age" field in escheat is greater than a global "TWO" field in status tracking.

                                   

                                  What would I use for the conditional count?  I tried Count (Escheat 3::Days_LastNotes (age)) > 0 and it didn't work.

                                  1 2 Previous Next