Set a field based on portal content

[Drew Vogel]

In my main database, I want to set a field from "NO" to "YES" if all fields in the portal have a specific field set.

Specifically, I've got a portal with academic transcript information, one line per institution. I set the RcvdTranscript field to YES once I've received academic transcripts from a particular institution.

In the main database, I need to create a calculation that sets from NO to YES if ALL the transcripts in the portal have RcvdTranscript set to YES.

This used to work under previous versions of FMPro, but with FMP8 it stopped...

If(Count(Rx Enrollment::RcvdTranscript) = Sum(Rx Enrollment::RcvdTranscript); "Yes"; "No")

Can anyone provide assistance for me on this, please?

[Matt Klein]

If you have a field in the Rx Enrollment table that is always populated such as an ID field, try this:

If(Count(Rx Enrollment::RcvdTranscrip t) = Count(Rx Enrollment::ID); "Yes"; "No")

[Drew Vogel]

Thank you for your reply. I'm not sure that does what I'm looking for...

Each student's portal record can have multiple transcripts (if they attended multiple schools).

I want the calculation to set a field in my main database (named PortalTranscriptRcvd) to "YES" when all transcripts for a particular student are received.

I hope that helps explain what I'm trying to achieve.

[Matt Klein]

Let's see if we are on the same wave length here....You have a layout that shows a single student record with a portal that lists the institutions for that single student from which you are expecting to receive transcripts.

Is that correct?

[Drew Vogel]

Let me provide an example, since I am afraid I'm not being clear.

The fields involved:

Rx Enrollment::RcvdTranscript is a YES/NO field in the portal record that is set to YES when a transcript is received.

RxSIS::PortalTranscriptRcvd is a YES/NO calculation in the main database that should calculate to YES when Rx Enrollment::RcvdTranscript is YES for all transcripts for the student.

Scenarios:

Joe Student applies. He's attended three schools and we've received transcripts from two of them.

Situation 1:

Joe Student

PORTAL: School 1: RcvdTranscript=YES

PORTAL: School 2: RcvdTranscript=NO

PORTAL: School 3: RcvdTranscript=YES

In this situation, the "PortalTranscriptRcvd" field in the main database should calculate to "NO" since all transcripts have not been received.

Situation 2:

Joe Student

PORTAL: School 1: RcvdTranscript=YES

PORTAL: School 2: RcvdTranscript=YES

PORTAL: School 3: RcvdTranscript=YES

Now the "PortalTranscriptRcvd" field in the main database should calculate to "YES" since all transcripts have been received.

Edited November 7, 2006 by Guest

[Drew Vogel]

Let's see if we are on the same wave length here....You have a layout that shows a single student record with a portal that lists the institutions for that single student from which you are expecting to receive transcripts.

Is that correct?

That's correct! Hopefully, my other post helps to illustrate it more effectively than I've done so far.

Thanks for your attention and help on this. It was working under FMPro6 but seems to have broken in the upgrade to 8.

[Matt Klein]

That's correct! Hopefully, my other post helps to illustrate it more effectively than I've done so far.

Thanks for your attention and help on this. It was working under FMPro6 but seems to have broken in the upgrade to 8.

The solution I gave previously will work in this scenario. The equation will compare a count of records that have anything in the Rx Enrollment::RcvdTranscrip t field in the portal and the total number of records in the portal.

I used ID in my example, because you need a field that will have data in every record in the portal in order for the Count() function to give you a count of all records in the portal. If you don't have an ID field in the table whose records are shown in the portal, then add a calculation field whose calculation is "1" so that it will be populated on every record.

[comment]

Since version 7, "Yes" or "Y" are no longer converted to 1.

If you change your RcvdTranscript field to Number, and use 1 for recieved and 0 or empty for not, you can use a calculation =

Count ( Rx Enrollment::StudentID ) = Sum ( Rx Enrollment::RcvdTranscript )

Note that number fields can be easily formatted to show non-zeros as "Yes" and zeros as "No".

You can also format the field as a checkbox using a value list of "1".

Edited November 7, 2006 by Guest

[Drew Vogel]

Thank you everyone for your replies.

Hrm... I think I'm getting closer to the solution, but it's still not working for me.

I've changed Rx Enrollment::RcvdTranscript to a NUMBER. It's Indexed, Auto-enter Data (a new record starts without transcripts, so the auto-entered data is "0" (is this the correct way to do this?)). I am not sure how to format the number field to show non-zeros as "Yes" and zeros as "No".

In the main database, I've configured PortalTranscriptRcvd as TEXT with a calculated value of "If(Count(Rx Enrollment::RcvdTranscript) = Count(Rx Enrollment::SSN); "Yes"; "No")".

This isn't working, and I'm not sure why. Can someone recommend a path for me, please?

Thanks again for all the help. This little field has proven to be quite valuable, and its absence is difficult!

[Matt Klein]

Here is a little sample file I just put together. Hopefully this will help you.

Test.zip

[Drew Vogel]

Matt, thank you very much for your test file and attention to my issue.

Your test file essentially does what I need it to do, but I cannot figure out how to get the same functionality implemented in my database.

I'm stumped and would appreciate any guidance on how to proceed.

[Matt Klein]

Do you have a field in the Rx Enrollment table that is guaranteed to have data in it on each and every record?

[Drew Vogel]

Yes, it's called SSN (it used to be the applicant's social security number; it is now a TEXT field which contains an index number that is unique for each applicant).

[Matt Klein]

Yes, it's called SSN (it used to be the applicant's social security number; it is now a TEXT field which contains an index number that is unique for each applicant).

I've changed Rx Enrollment::RcvdTranscrip t to a NUMBER. It's Indexed, Auto-enter Data (a new record starts without transcripts, so the auto-entered data is "0" (is this the correct way to do this?)). I am not sure how to format the number field to show non-zeros as "Yes" and zeros as "No".

In the main database, I've configured PortalTranscriptRcvd as TEXT with a calculated value of "If(Count(Rx Enrollment::RcvdTranscrip t) = Count(Rx Enrollment::SSN); "Yes"; "No")".

OK, so assuming the above, the PortalTranscriptRcvd field likely always contains "Yes". This is because you have the Rx Enrollment::RcvdTranscrip t field set to auto-enter a "0" when a record is created. So, if you Count(Rx Enrollment::RcvdTranscrip t) you will get a number that is the total related record count which is exactly what Count(Rx Enrollment::SSN) does. As a result Count(Rx Enrollment::RcvdTranscrip t) and Count(Rx Enrollment::SSN) will ALWAYS return the same number and your PortalTranscriptRcvd field will always be "Yes".

Why is that? The Count(field) function returns, in this instance, the number of related records in which the field referenced in the Count(field)function is NOT EMPTY. So, by auto-entering data into the Rx Enrollment::RcvdTranscrip t field you are including that record in the Count.

So, the solution is to change Rx Enrollment::RcvdTranscrip t back to a text field and remove the auto-enter settings from it. There should only be something in that field if a transcript is received, otherwise it should be empty.

[comment]

At the risk of confusing you even further, I am going to disagree with Matt and recommend you keep the field as type Number. In general, it is much more convenient and efficient to code Boolean values as 1 (true) and 0 or empty (false). Filemaker itself follows this convention - see, for example, the IsEmpty() function.

There are many reasons why this is preferable - I will only mention a few:

1. As you have noticed, Matt's example only shows "Yes" for transcripts received, but nothing for transcript pending. He cannot show "No" in the same field, because (as he correctly states) the Count() function counts ALL non-empty values. Similarly, the Sum() function is useless for text values.

A number field, OTOH, can be formatted to show zeros as "No" and non-zeros as "Yes" (or any other labels, sadly limited to 7 characters). Select the field in Layout mode, and choose Format > Number... from the menu to do so.

2. If you need to test a text value, in a calculation field or in a script, you need to state it expressly in the test. Say you have a Paid status field for marking a record as either "Paid" or "Pending". In such case, the test needs to be:

Case ( Paid = "Paid" ; ... )

Now suppose you want to change paid bills to display as "Settled" instead of "Paid". You need to

(a) modify the value list,

( find all "Paid" records and replace their value with "Settled", and

© modify all calculations and script that rely expressly on the "Paid" value being there.

With a number field, the calculation is simply:

Case ( Paid ; ... )

and to change the display you only need to reformat the field.

3. You cannot base a relationship on an empty field. If you ever wanted to filter the portal to show only received transcripts, or only pending transcripts, or both, you'd find you need to add a calculation field like:

Case ( RcvdTranscript = "Yes" ; 1 ; 0 )

Which translated to plain English reads: 'I should have made the field a Number in the first place, but I didn't, so now I need another field to do it for me'.

4. You don't even need a value list for a Boolean field. See the attached example.

MarkCompleted.fp7.zip

[Drew Vogel]

Thank you for your replies, everyone. I appreciate the willingness to help. Comment, I've downloaded your example and will examine it in a moment and will report back with any questions.

I am confident that this can be solved and appreciate everyone's help.

[Drew Vogel]

Comment... Thank you for your sample. It does precisely what I am looking to do.

I will attempt to integrate the ideas you presented into my database and will report back with my progress. Thanks!

[Matt Klein]

Good stuff Comment.

[Drew Vogel]

Hello again everyone.

I want to thank everyone (especially Matt Klein and Comment, who coded examples for me) for their assistance on this issue.

I haven't gotten a chance to try to implement what Comment suggested (it's been busy at my office and my boss experienced the sudden loss of her father), but my busy schedule in no way diminishes my appreciation.

I _should_ be able to look into implementing it on Wednesday, unless something else happens... I will report back with my success or any additional questions.

Thanks everyone.

Edited November 21, 2006 by Guest

[Drew Vogel]

I am still struggling with this issue... No matter what I try, I cannot seem to get it to work.

Currently, I've created a calculated field in my main database with the following calculation (that always calculates out to "1", no matter the setting of the RcvdTranscript field)

If(Count(S.S.#Year ID)=Sum(Rx Enrollment::RcvdTranscript); 0; 1)

"S.S.#Year ID" is a new field I've created in the main database that is a combination of the student's ID and the year of their application (we get many multiple-year applicants).

Unless someone can offer a better suggestion, I am nearly to the point of emptying the database of private information and posting it here for you to look at (though I shudder to think about the pros here going through my rag-tag code)...

Edited December 28, 2006 by Guest

[Matt Klein]

Hi Drew. Well the field("S.S.#Year ID") that you are counting using the Count() function is in the Main Table. As a result, Count(S.S.#Year ID) will always be equal to 1. If there is an ID field in the Rx Enrollment table try changing your calculation to:

If(Count(Rx Enrollment::ID)=Sum(Rx Enrollment::RcvdTranscrip t); 0; 1)

You want a total count of records in the portal which Count(Rx Enrollment::ID) gives you.

I attached a file that is essentially my original sample I attached in an earlier post with some changes that use Comment's suggestions.

Let me know if you have any questions after you look at the attached file.

Hope this helps.

Test.zip

Edited December 28, 2006 by Guest