Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Newbie Question About functions to extract info


phosphor

Recommended Posts

I'm pretty new to filemaker, and am having trouble figuring out how to extract certain values for a sort of report. What I'd like to do is have a count performed on each value in a value list. I was thinking a calculation field that combines the various valus with carriage returns between each value. It's for a personal movie database and what I want to do is go to a report page and see various statistics. In this case it has to do with genre, I want it to find each value that exists in field:genre, perform a count of those values, and average(Rating), and then move to the next value. I'd also like to be able to do this with year. I'm pretty sure I know how to do the text results with & and the carriage return, but I'm not sure how to cycle through the values in genre, and return the count and average rating. Any and all suggestions woul be greatly appreciated. Thank you in advance.

Link to comment
Share on other sites

I guess I wasn't too clear, sorry for anyone who took the time to read but idn't understand what I was asking. What I'm working on is a Movie Database. Some of the fields are rating year, and genre. What I'd like to do, for examle is count the number of entries with each value in genre, and the average rating for that group. Thus if the genres where say Action, Suspense, and Horror, I'd want something like:

Action 12 movies avg 6.3

Suspense 5 movies avg 5.6

Horror 22movies avg 5

I'd also like to do the same by year. So I guess what I need is a way to cycle through all the values in a given field, count the number of thos values, and a produce an average of the rating field for those records. Thanks in advance for any and all suggestions.

Link to comment
Share on other sites

No, what you need is a report layout with a sub-summary part (when sorted by Genre), and a summary field (Count of RecordID). This will give you the count of entries in each genre, and in total.

 

Another summary field (Average of Rating) will give you the average rating of each group and in total.

Link to comment
Share on other sites

I don't want to use a subsummary, thats the thing, I mostly have this set up on a printable report, but the fact that it has to be displayed in preview mode means it looks terrible on my laptop. The average was easy, I'm having trouble with some time calculations that only have one decimal, but otherwise I have it mostly set up like you suggest, but it looks terrible and hurts the eyes. And then theres the fact it has to be sorted a certain way for the summary to work. This is great when I want to print things, but for display on the puter I want to be able to see at a glance all the vital stuff, count and average by genre and year etc. Thanks for the reply. I appreciate any an all suggestions.

Link to comment
Share on other sites

If you don't want to use sub-summaries (and it would be easier had you mentioned this before...), then I suggest you create a table of Genres (if you haven't done that already) and create a relationship from Genres to Movies:

Genres:Genre = Movies::Genre

 

Then use calculation fields with aggregate functions to create your layout in Genres. A calculation field of Count ( Movies::MovieID ) will return the count of entries in each genre, and Average ( Movies::Rating ) will give you the average rating of each group.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use