Related field outside portal showing odd results

[onthebass]

Hi everyone,

I recently moved one of my solutions from FM 6 over to FM 8.5 and I'm discovering a whole new world.

Almost everything is working except a relationship I have set up for a field sitting outside of a portal. So the field is supposed to show just the first record of that relationship, correct? Well the field is showing a result even if there is NO data in the key field, and even if there are no records in the related file for it to match!

The field in the related file, let's call it Appt Status is a container calculation that calculates the appt status and returns the results of one of several global containers that have a color in them. So if the appointment is "new" then the result will be a green container. This calc works fine.

The relationship is based on a calc, Patient Id_Date (text)

= patient id & " - " & getasnumber(date)

Both key fields are Text.

It's funny the relationship works fine if the field is placed in a portal.

This set up worked fine in FM 6. So does anybody know what the problem could be.

Thanks a million!

[onthebass]

Ok, I got another clue for you.

I changed the related field Appt Status to another field from the same related file that is a text field and it worked fine.

So it must be having some issue with the container.

Chew on that.

[onthebass]

Okay, more clues.

I set up the almost exact same scenario with two brand new blank databases. It worked fine with both tables in the same file and with the tables in two separate files.

I'm going crazy!

[onthebass]

It must be a bug. That's the only thing I can figure.

I created a new relationship with simple keys in each of the files and still it returned results even if the key fields were empty.

I also created a similar scenario using a table in the same file as the master record, as opposed to a separate file, and it worked.

I tried recovering the file too, just in case it is file corruption. Nothing. It is either something really obvious and dumb, or a bug.

Anyone?

[onthebass]

Okay, I'm basically entertaining myself here. I can't believe no one has anything to say on this.

Well I tried one more experiment. I changed the related field to a text field that was a global and it gave me the same results...:

So maybe it has something to do with globals. The Appt Status calc has a result that comes from a global container field, so I don't know if that is related to the problem or not?

I guess I need to eat. Cheers!

[onthebass]

It's funny how I always figure a problem out when I stop to go to the bathroom.

Check this out, it *is* a problem with a mystery record producing a result that the portal picks up. I solved the problem by putting a condition in my case statement for Appt Status calc.

Here is the original statement:

Appt Status =

Case(PatternCount( Visit Status; "Cancelled") > 0 or PatternCount( Visit Status; "No Show") > 0 or PatternCount( Visit Status; "Rescheduled") > 0; Cancelled Flag;

PatternCount( Visit Type; "History") > 0; First Visit Flag;

PatternCount( Visit Type; "Reevaluation") > 0; Reevaluation Flag;

PatternCount( Visit Type; "Conference") > 0; Conference flag;

; Appt Flag)

Now I added the condition which wouldn't allow the calculation to return a result unless there was a patient ID, i.e., an existing record:

Case(PatternCount( Visit Status; "Cancelled") > 0 or PatternCount( Visit Status; "No Show") > 0 or PatternCount( Visit Status; "Rescheduled") > 0; Cancelled Flag;

PatternCount( Visit Type; "History") > 0; First Visit Flag;

PatternCount( Visit Type; "Reevaluation") > 0; Reevaluation Flag;

PatternCount( Visit Type; "Conference") > 0; Conference flag;

[color:red]not IsEmpty(Patient ID); Appt Flag)

It's crazy that even though there are NO records in the related database the pure existence of this calc returns a value.

Goodnight everyone!

[Lee Smith]

I thought I would let you figure it out by yourself, you seem to be working hard on it and having a wonderful conversation with yourself.

All kidding aside, there is a few white papers at FMI that explain how you should migrate a file from version earlier then 7 and 8 into 7 and 8. Without seeing your file(s), I would only be guessing what the problem might be.

Have you checked your file references?

Lee

[Ender]

So maybe it has something to do with globals. The Appt Status calc has a result that comes from a global container field, so I don't know if that is related to the problem or not?

I can't tell which field this is in your calc, but it's important to remember that if your calc has a global, related field, or unstored calc, it cannot be used as a match key on the child side of the relationship.

If this is what you've got going on, then you'd need to rework your calc or relationship so that it doesn't use those.

[Søren Dyhr]

Flagging or tagging, is relational unhealthy and a very common misconception, a FAQ I'd dare say! Only GetSummary( accepts unstored values as breakers, otherwise is a scripted replace required... which ofcourse is seemless when 10-15 records needs it but when deling with say 20000 records... not to mention the record ownership in multiuser environments.

--sd

[onthebass]

I thought I would let you figure it out by yourself, you seem to be working hard on it and having a wonderful conversation with yourself.

All kidding aside, there is a few white papers at FMI that explain how you should migrate a file from version earlier then 7 and 8 into 7 and 8. Without seeing your file(s), I would only be guessing what the problem might be.

Have you checked your file references?

Lee

White papers, shmite papers. They don't tell you about the real anomalies that occur. I always have more success just doing a google search.

Anyway I am making my way through them. The only reference I could find to globals was:

If you are going to deploy the solution using FileMaker Server, be sure to do at least some of your testing with that

confi guration. It’s highly unlikely that you’ll get differences in functionality, but performance can fl uctuate, and there are

some things, such as fi elds with global storage and some of the Get functions, that may behave differently when hosted.

That isn't much help is it?

Thanks anyway Lee!

[onthebass]

I can't tell which field this is in your calc, but it's important to remember that if your calc has a global, related field, or unstored calc, it cannot be used as a match key on the child side of the relationship.

If this is what you've got going on, then you'd need to rework your calc or relationship so that it doesn't use those.

No all match keys are stored calcs - Patient ID & Date. The relationship works fine it's the results that are quirky.

Thanks!

[onthebass]

Flagging or tagging, is relational unhealthy and a very common misconception, a FAQ I'd dare say! Only GetSummary( accepts unstored values as breakers, otherwise is a scripted replace required... which ofcourse is seemless when 10-15 records needs it but when deling with say 20000 records... not to mention the record ownership in multiuser environments.

--sd

You are definitely right, it is not prudent to put Summaries into portals. What I'm doing is basically just getting the result of a calc in a related table that has a global container as its result. One record, one calculation.

I've attached a couple of images that show what happened before I made the adjustment to my calculation and after. Notice how in the before all the calendar dates are blue. They are only supposed to have a color if the patient has an appointment that day, i.e. there is a record to match the patient Id - date. Then in the after picture the calendar is looking like it should.

But you are probably right that I could have also fixed this problem by setting the field with it's proper color rather than using a calc to give it it's color. It's just a curiosity to point out.

By the way, I actually am also using Summaries in a portal in the same database, and it is for just counting a few records at a time. If you look to the left I have a portal that lists all the appointment slots and shows how many appointments are scheduled for that slot. Since there can only be a few appointments per slot it's very fast at doing the calculations.

Speaking of speed, I'm finding that FM 8.5 is noticeably slower performing the same tasks I'm doing in 6. And I did clean up all the file references.

Thanks for your reply Søren!

[comment]

It would be easier if you would attach a file showing the problem - preferably in isolation. As it is, one can only guess. My guess would be that your relationship is allowed to create records, and the "mystery record producing a result that the portal picks up" is the 'ghost' or 'phantom' record waiting to be born in the last portal row.

[Søren Dyhr]

By the way, I actually am also using Summaries in a portal in the same database, and it is for just counting a few records at a time.

You mean the aggregatefunction Sum( ?

Speaking of speed, I'm finding that FM 8.5 is noticeably slower performing the same tasks I'm doing in 6. And I did clean up all the file references.

What do you need file references to, when you consolidate everything into one file??

There is in such a solution not a need to use a container field to colour the fields, use unicode signs with calculated colours via text styling and layer them underneath the actual text.

But yes I'm with Michael, lets have a poke into your files relational structure. While you perhaps make an revisit to the migration foundations whitepaper.

Speaking of the time it takes randering, how many relations do you have in the tiny calendar? There should only be one, based on a multicriteria ...well take a look at JMO's template:

http://www.filemakerpros.com/CALBASIC.sit

--sd