Counting by type in Portal

[ianokeefe]

I have a database that tracks events at locations in cities. Each event has a Unique ID. On my cities form, i have a portal that shows the List of locations in that city for each event. It also shows the Event ID for each location. Because locations have multiple events, the locations will show up more than once with different Event IDs., and each event may have more than one (or no) locations.

 

 

This is the portal in my layout. What I'm trying to do is get an average for each city of the number of locations per event. I set up two summary fields in the Lists table (which  is the table shown in the portal). One is supposed to calculate the number of events, the other calculates the number of list items for the city. Once I have these two, I can calculate the average. My summary field for the number of events doesn't work. I have it set up as Summary = Count of Event ID (running with restart), when sorted by List::Event ID - which is the field the portal is sorted by. Is there an easy way to calculate the number of different event id's in the portal? Example - if the event ids are 3,4,5,5,5,6,6,7 - I want it to return 5 because it represents 5 events. Thanks!

[comment]

Can you explain what tables do you have and how they are related? It seems like you should have something like:

Cities -< Locations -< List >- Events

With this arrangement, you can calculate the number of distinct events taking place in a city by =
 

Count ( Events::EventID )

 

[ianokeefe]

There are only two tables with actual relations:  Cities --< List

The Locations are contained within the records of the List table. We are provided a list of locations which is imported into the list table, and the event ID is plugged in manually at that time, but there is no explicit relationship between the Events table and the List table. I know I could just write a script that cycles through each record the cities table, gets the City ID, goes to the List form, searches for matching ids, loops through those, counts the different event ids, goes back to the Cities form, plugs in the value, and moves on to the next record, but I'm guessing there's a much more elegant way to do it.

 

[Edit] Okay, I get it - If I make a relation between events and list I can put a 2nd portal with a summary field with the event count on the layout and a script can collect those values to get the average, but what if it's something that's not in another table?

 

For example - If a List record had a field that described what type of location it is (sports bar, irish pub, taqueria, martial arts studio) - is there a way to count the different types of locations shown in a portal for a specific city? So if there were two irish pubs and 3 sports bars, the number would be 2 because there are only two types of locations.

[comment]

In the absence of an Events table, you could do the following:

 

1. Define a value list named "EventIDs", using the List ::EventID field;

 

2. Use this expression (in the context of the Cities table) to count the number of distinct related events =

ValueCount ( FilterValues ( ValueListItems ( Get (FileName) ; "EventIDs" ) ; List ( List::EventID ) ) )

Alternatively, set the value list to include only related values starting from Cities, then reduce the expression to =

ValueCount (  ValueListItems ( Get (FileName) ; "EventIDs" ) )

(both untested)

 

 

You may want to consider renaming the List table to something else, since LIst() is a function and you can see how it may be confusing. Also, every table is a list of some kind and it is useful to have a more informative name, e.g. Activities or Happenings.

[ianokeefe]

Thank you so much. The table is actually named 'Legal List.' Here's the structure now: Cities -< Legal List >- Events. So I related the table to the Events table, and created the two single item filtered portals containing the summary fields. This works well, except in some records where all the records are filtered out - then the script that collects the values of the summary fields and calculates the average gets returned the UNFILTERED counts. I'm filtering by event type - in this case 'boxing.' But on cities where there are no boxing events it's giving me the counts of 'UFC' events. The type field (that is being filtered) is in the Events table. The fields on the form are blank in this case, but the script gets returned the unfiltered counts from the summary fields.

[comment]

If I make a relation between events and list I can put a 2nd portal with a summary field with the event count on the layout and a script can collect those values to get the average,

 

This is an unnecessary complication. Once you have the relationship in place, use the calculation provided in post #2.

 

 

This works well, except in some records where all the records are filtered

 

Calculations work at the data level; portal filtering is applied at the presentation level. If you want to use a filtered result in a calculation, the best approach would be to create a filtered relationship (as opposed to a filtered portal) and use that.

[ianokeefe]

Again, thank you. No good deed... I haven't used filtered relationships, I am guessing I did it wrong: I set up a Calculation field called BoxingConstant in the Cities table, Global, From Cities, ="Boxing"

Then I set up a Calculation field called EventType in the Legal List table, Unstored, = Events::Type

 

and I turned off filtering in the portal

 

and I added AND BoxingConstant = EventType to the relationship between Legal List and Cities.

 

Now nothing shows up in the portal

 

This is what the relationship looks like.

 

 

[comment]

You cannot base a relationship on unstored calculation fields on the "other" side of the relationship.

 

I am somewhat confused here, because you seem to be mixing two different approaches designed for two different situations. If you do have a relationship to the Events table, you can get data directly from there. For example, open the Data Viewer and have a look at the result of:

FilterValues ( List ( Events::Type ) ; "Boxing" ) 

(while you're in a layout of Cities).