How to obtain counts from a related db

[BFH]

Sorry if this message really belongs in one of the other forums.

My college-student information tracking system includes (among lots of other categories of information) publications, posters, and oral presentations.

For each of these three categories, I need to track such information as title, date, where, etc.

The upper limit for the number of publications, posters, or oral presentations for any given student is indeterminate in advance since the number can continue increasing during the student's career. Some students could be quite prolific, depending upon their research project.

I am keeping each of the above categories of information in its own, related database so that the master is not overwhelmed with fields and records for tracking all of the above. So I have a Pubs related file, for example.

In the master, I have created a portal containing the necessary related fields into which data-entry staff will type the relavent information. So far, it works properly; when I type in a publication title, for example, the master displays that title and a new record is created for that student (ID is the key field) in the Pubs file which now contains the title.

My difficulty now is that the master needs to display automatically the following: 1) the total number of publications for each year for each student; 2) the cumulative total number of publications (across years) for each student. If a data-entry person types in, for example, 2 articles in 2002 and 3 in 2003 for a given student, the master needs to display 5 as the cumulative total, 2 for 2002, and 3 for 2003.

I scoured these forums and looked over some of the samples, etc., and have not found an analogous scenario. What I did find, however, was a tip to create a calculation field in the related file that contains the command Status(CurrentFoundCount), and then put that field in the master. The field works, but it is tracking *all* students' total. I need to be able to get values for individual students.

Could someone please point me in the proper direction for this? Many thanks in advance!

[Razumovsky]

You are looking for the functions:

Count

Sum

GetSumary

These can be calc fields that access related data. Check out the FM help manual for the specifics of these functions, but it sounds like they will do the job.

-Raz

[Pupiweb]

Several ways to tackle the problem

The easiest and most flexible way would be that of having some subsummary layouts in the Pubs file so that you might have a report broken by student and year ... this however only works in preview mode

Another nice way of displaying data is a filtered portal:

Create a gYear field in the Students db, and a calc StudentID & gYear

Create a calc field in the pubs db:

StudentID & "PP"

StudentID & PubYear

where "PP" stays for a carriage return

Relate the two fields and create a PubCount field, calculated as:

Count (ThisNewRelationship::StudentID)

Now when you leave the gYear field empty a portal will show you all the student's publications and the PubCount field will tell you how many they're; entering a year in gYear will show the values for the selected year

HTH

[BFH]

Thanks for taking time to reply.

Does gYear field signify a global field named Year?

"Filtered portal??"

Sorry for my ignorance; I'm looking for a pill to fix that.

[LaRetta]

Hi BFH,

We meet again

Exactly The g represents global. But, I just noticed something ... gYear! Uh, what data-type are you fields? The reason I'm asking (and I haven't been tracking this post), is because I see above an example of a calculation using something like StudentID & gYear. What is the data-type of your gYear global field?

I just think that, as you work this out, you should keep in mind your data types and be sure to convert any number or date fields to text if you use them mixed with text fields.

LaRetta

[Ugo DI LUCA]

Hi La Retta,

As long as g_year and year use the same format, the calcs Student_ID & g_year and Student_ID & year would have the same results.

I think there is therefore no need to convert these in text in Pupiweb's solution.

[Pupiweb]

Sorry, I left some details out ...

Yes, gYear is a global text or number field; the calc field StudentID & gYear is text field; the calc field (in the related db) StudentID & Year (PubDate) etc .. is also a text field

[BFH]

Hi LaRetta!

Thanks for your reply and helpful suggestions.

However, I haven't created any global fields yet or implemented any of the suggestions in this thread because I'm still not understanding these replies to my post.

Can you explain a "filtered portal" (or point my nose in the direction of the pages in the User's Manual, or elsewhere on the web) that contains a basic explanation of this concept? In particular, I need to understand why filtered portal is what's appropriate to meet my need.

And I don't understand why a global field is what meets my need.

[Ugo DI LUCA]

Hi BFH,

Filtered Portal...Hmm...

I've just posted somewhere an example for a library with

Library Themes

Books

Chapters.

Suppose you are in Library Themes file. You've got a portal from Library to Books displaying all books belonging to the same theme (Geography, History) using a relationship (Theme_ID::Theme_ID).

Suppose you want to have a list of all Chapters from one specific book.

The relationship from Book to Chapter is Book_ID::Book_ID, but there is no relationshipo from Library to Chapter.

That is one example were the filtering portal is used...

To display these information, you will use a global field to "temporary" store the Book_ID from which you want to see the Chapters.

In that case, you will use a script in the portal row of your Books portal, that will set the global to the Book_ID selected.

Then you will draw a new portal using this g_Book_ID for a "temporary" relationship from Library to Chapters. The relationship will be Library:g_Book_ID::Chapters:Book_ID.

For your specific calcs, you will even have an example there as I use this kind of calculation to identify the number of chapters per book.

See the Attachment section At the Entrance and look for a file called "Library".

If you don't mind, even if you didn't ask for it, in your particular case, I would see a need for a global in your master file to display (and edit) new entries for Publications, Oral presentation and Posters. I suppose you hold all these information in their own portal/layout right now...

A global field would help jumping from one Publication to an Oral Presentation without having to change layout, nor portal, using that global as a value list...

You will have a lot of other examples on this forum though...

[BFH]

Hi dilucaugo68 and many thanks for taking time to reply.

You've done a good job of explaining and what you've written is clear, but I am ignorant (I'm not a professional or amateur programmer) and therefore do not completely understand. Suffice to say that if I tried following your directions, it wouldn't work because I lack the conceptual clarity to implement your directions.

Is filtered portal a built-in feature of FMP (not described or mentioned, as far as I can tell, in FMP's Help or User Guide), or does it require additional software (plug in)?

Thank you for your suggestion to use globals to display and edit entries in Publications, Posters, and Talks. I did not know/realize that a global field was necessary there. Instead, as you surmised, I did create separate layouts.

Actually, from a user-friendliness perspective and my naive ideas about data-entry design, I want to keep those three categories of information in their own layouts. I am trying to keep each layout as minimalist as I can in terms of the amount of information and clutter as I can.

The tracking system has lots more categories of information and it likely will be overload for the data-entry staff if too much is on one layout.

[Ugo DI LUCA]

Hi,

Is filtered portal a built-in feature of FMP (not described or mentioned, as far as I can tell, in FMP's Help or User Guide), or does it require additional software (plug in)?

No it's not a plug-in. It's a relational trick.

Thank you for your suggestion to use globals to display and edit entries in Publications, Posters, and Talks. I did not know/realize that a global field was necessary there. Instead, as you surmised, I did create separate layouts.

Well actually, as you keep Posts, Publications and Talks in separate file, and you do not implement a Line Item, it would have needed some more implementation to work. A global is currently used to moove in depth into one file, not in multiple files (at least in a simple implementation)

.

If you were using a line item, calcs and filtering would also be easier as all the information would be stored into these lines. But this is another discussion...

[BFH]

Many thanks for taking time to reply again.

This has gone beyond the scope of my understanding.

I'll try to do some additional searching through the forum on filtered portals and see whether I can make sense of it.

Actually, do you recommend one of the FMP books as a good place to learn about filtered portals?