This seems like a simple thing but I'm not finding it.
I have a table where I want to count records from another table in which an ID field and a valuelist field match. The value list is represented in the related table with text values on separate lines.
I want to create a calculation field in the first table that simply looks at the other table and give me the number of records meeting multiple criteria. I'd like NOT to use SQL if possible, just because of my ignorance with it.
Any thoughts?
funny I did that just now.
I will include my test file here. Look at the relation TeacherAttendanceWorkdaysList and Workdays. The fields DatesInSpan and WorkdaysInSpan are what you are looking for.
DatesInSpan is a list of identifiers (here unique Dates) which I match with the field Date in the table Workdays what I want is for each date in the list the corresponding Workday. I get that by putting List (Workdays::workday_N) in the WorkdaysInSpan field definition.
I am in the process of figuring out where to put the following calculations that i tested in the data viewer your situation doesn't need the first 2 ones; you just need the ValueCount(RelatedTable::MyCorrespondanceField)
TeacherAttendanceWorkDaysList::WorkdaysInSpan & "¶" & InstallationAvailibilityWorkDaysList::WorkdaysInSpan
(UniqueValues ( TeacherAttendanceWorkDaysList::WorkdaysInSpan & "¶" & InstallationAvailibilityWorkDaysList::WorkdaysInSpan ))
ValueCount ( (UniqueValues ( TeacherAttendanceWorkDaysList::WorkdaysInSpan & "¶" & InstallationAvailibilityWorkDaysList::WorkdaysInSpan )) )
TestWordaysAttendance.fmp12 (592 KB)
Let's use a shopping cart example.
Shopper 1 has a cart containing mushroom, tomato, apple
Shopper 2 has a cart containing banana, bread, apple
Shopper 3 has a cart containing bread, milk, beans
if we use a global field to create a relationship between shoppers and carts we could put apple into the global field and match two records. Provided we only use one criteria that will provide the correct result.
If we use a multi-key global field the relationship will provide the UNION of the matches for all the different keys. For example, if the global contains apple and banana, it matches two records. Again that is correct.
If you want to specify that each key in the global field must be matched. That is, match carts that contain apples AND bananas, then you need to have more than one criteria in the relationship. In other words, you need two global fields, one to contain "apple" and the other to contain "banana". Then the relationship will only match one record.
The more criteria you have, the more fields you need. Also, you need to provide a relationship for one criterion, a different relationship for two criteria, and a different relationship for three criteria, and so on.
If you have known limits then this may be easier. However, using executeSQL would allow you to generate the query on the fly. Then you could use any number of criteria. This would allow an all-purpose script that has no limitations.
Thanks for this. My second table has one record per person with summary information (ie singer name, set 1 count, set 2 count and so forth, along with a GigID.
In my Gigs table I a field for GigID and want to pull each of the corresponding records. So GigID=GigID.
So I have a relationship made based on GigID.
But it doesn’t work.
If the GigID is on a line by itself then that should work.
I’m actually trying to populate a portal from the second table to Gigs based on that field.
FM uniquely supports multi line predicates. This means that a global field in the parent table can have a carriage return delimited list of items related to a child field where if any one of the list matches, the relationship is true.
Your value list can populate this parent field with the value list items. VALUELISTITEMS ( filename ; valuelistname )
You can also add a multi predicate relationship (more than one concurrent relationship.
If, in your child table there is an UNSTORED calculation field calculating Get ( FoundCount ) it will dynamically return the found count of the found set of child records based on the filter provided through the relationship.
I just can't figure out what I'm doing wrong..... I'll share some layout screen grabs and explain what I've done - maybe that will clarify.
This is my gigs layout. The top blue section is a navigation header, so one per record in Gigs.
1 is a portal to gig_PERFORMANCES which is a join table and ties information to the GigID (138 in this case).
2 is a portal to SetListSummary
This is the Set List Summary layout where there is one or more records for each performer in that Gig.
1 are a series of fields that count the number of songs performed by a singer (in this case LS) who has 2 songs in set1, 3 in set2, 0 in set3, 1 in set4 and 1 in set5. They also have 2 songs yet to be assigned a set (S0).
2 is a list of songs in which singer LS is performing.
This layout works beautifully.
My goal is for the (2) portal on the first layout to display the count information (1) from this second layout.
This is the Relationship graph I'm using.
It seems as though the only field you need in the relationship is the GigID on each side.
If you want a count of records in a given grouping/found set, a summary field [count] will give that to you.
Alternatively, a child table (self join?) with a multi-predicate set of match fields, and an unstored calc field "get (foundCount" in that table, will display the record count, by using the child side of the self join
