What is going on?

[ron G]

I am running FM 12.04 Advanced in OSX and Windows.

(I hope this situation isn't so complex I can't explain it properly)

 

I have a Membership application.

 

On the Membership layout there is a DUES portal (based on the dues table which is 1 : many with Members)

 

This works great.

 

But, I created a simple Sub Summary report that is based on the Dues table.

 

The first Sub Summary is based on Dues_Status ("Current or Late")

The 2nd Sub Summary is based on Member::NameLastFirst

 

Of course I sort on these two fields

 

 

 

In the Dues portal there is a Dues::MembershipYear field.  (Here is the weird part)

 

If Dues::MembershipYear is empty, it does not show on the report.  Despite the fact that I can see the Membership window and see that all 62 records are showing, the blank Dues::MembershipYear fields will not show on the report.

 

If you've had a similar experience I would like to hear about it.  Thanks

[Josh Ormond]

That usually means that you have the Membership Year field included in the relationship somewhere. If it is, it can't match something that has no value.

[ron G]

Rats!.  It doesn't look like that is the situation.

[Lee Smith]

What is the make up of the ID?

[ron G]

Both ID's are numeric.

[ron G]

OK.  In the interest of being clear. 

Here is the main Membership Window showing the Dues portal below (It is the last row preceeded by a red "X" )

for Member Carleton Dowson.

 

Note:  Dowson does NOT have a Dues record in the portal row.  If I add a Dues record in the portal, his name shows in the report.  Huh?

 

Notice in the report below Carleton Dowson's name is missing because he does not have a Dues Record in the portal.  If I add a dues record in the portal, his name shows in the report.

 

 

Here is the script that generates the report.

 

Here is the Sort:

 

Aside from this little weirdness, the script and report work great. 

 

Thanks for your thoughts.

[ron G]

Here is another weird situation.

A Member named Joe has a MemDuesStatus field value of "Current" .  But has no entry in the Dues Portal.

 

As I step through the script, even at the step where the layout is brought into the new window, "THERE ARE NO RECORDS WITH BLANK DUES values"?  My found set clearly shows Joe without Dues rows in the portal but those records are never recognized.  Is there some value I need to turn 'off'?

[Josh Ormond]

Do you have any script triggers, layout or otherwise, that may be firing and changing it up on you.

[ron G]

No script triggers including layout triggers.

 

I tried making the script really simple.  Load the layout.  Sort (To activate the sub summaries) and the blank portal rows still do not show.    It occurs to me that one of the sorts is on the date.  So, if the date field is empty then that row does not show.  If this is it, how do I get it to show?

[Josh Ormond]

The sort would then only move where they show.  Do a constrain find and see if the records are still in the record set.

 

So either there is something, somewhere that is altering the record set...or the record is not where you expect it to be...or there is some level of corruption.

 

If you need help diagnosing...I would recommend uploading the database so we can better see what is going on.

[ron G]

Thanks Josh

I created a new layout with just a minimum fields.  Ran the same scipt (specifying the new layout) and got the same result.  While in that new layout, I went to a record without portal information and entered the date.  Boom.  It appears in the report.  Then I delete the row.  Pop.  Gone from the report.

 

I think this eliminates a corrupted layout.  And, since I can 'seen' the missing data in the found set ...  well, I am stumped. 

 

If you think I still should send the database, I will ... (55mb).

 

Ron

[ron G]

I think I 'got' what is going on.

 

The layout is based on the Dues Portal.  (Since I need the detail of each record in the portal)

 

When FM tries to create the Sub Summary for Dues and does not find a record, it skips the subsequent Member record as well.

 

This seems logical since when I add a blank row the portal (so that there are 2), the Member record shows.  Now I just have to find a way to work around adding a dummy record to the Portal....  Ideas???

[Brooks]

You can also create a reports table where you push the data from the related tables and basically flatten your database for the purpose of reporting. There are a few ways of doing this including a normal looping script, using a portal, or export import with update existing records. If you want to attach a copy of your db and it is 55 mb, create a clone copy and add a few sample records. Of if your ui is that big create a sample file with just the components you want us to look at.

[ron G]

It seems that what I am trying to do should be done internally by FM.  After all, my layout is based on the portal (The 'many').  The records sorted are based on fields from the "One" (Members) and FM KNOWS and approves this relationship.  But, when an empty portal row shows up (ie, there are no records in the portal), FM gets confused and omitts the entire record.  Weird...

 

Surely I am not the first person to 'discover' this new FM 'feature'

 

[bruceR]

No, the layout is not and CANNOT be based on a portal. You are confused about basic, clear, documented features of Filemaker design.

[Josh Ormond]

Extract out all but the records that show the problem. If you want to send it back channel to me to look at that is fine.

 

 

If you think I still should send the database, I will ... (55mb).

 

Ron

[Josh Ormond]

So looking at your screenshots again.  your sub-summary report is based on the dues table.  If that is true, that is why it can't show you someone that has no records there.  There is no change in this.  You are looking at the break fields (the person) thinking all the people should be there. But in reality, the table is a list of the dues, and groups them all by the people associated with it. If there is no dues for a person, it can't group records that don't exist.

 

It seems that what I am trying to do should be done internally by FM.  After all, my layout is based on the portal (The 'many').  The records sorted are based on fields from the "One" (Members) and FM KNOWS and approves this relationship.  But, when an empty portal row shows up (ie, there are no records in the portal), FM gets confused and omitts the entire record.  Weird...

 

Surely I am not the first person to 'discover' this new FM 'feature'

[ron G]

Absolutely right.  That is how I see it also.

Since I break on the name, why does it matter that there is no record in Dues?  Shouldn't the name appear by itself?  Also, if I enter a blank row in the portal (Dues) everything works as expected;  Name with blank portal fields.

[Brooks]

This is kind running in a circle, but ill give it one more shot. The report layout is based on dues. Look at the layout in table view and show all records. There is all your data that the report is can be based on. Those are your parent records. Bruce was trying to point out a layout can not be based on child records ie portal. The layout is based on a table, and that tables records are the parent records.

Now that we have established your layout is based on dues as the parent record now you can reverse the scenario you have been explaining. In reality the contact would be the portal data and the dues the parent. If you were in form view looking through the dues records with a portal showing the contact, would ever see a contact in the portal that didn't have any dues? Of course not! So in a report based on dues you will never see a contact that doesn't have a dues record.

Now if you must see all contacts for this report create a reports table and do what I suggested earlier and import the dues data and then loop through your contacts and create a blank record for each contact. Now you can see all contacts even if dues is blank and you won't muck up your actual dues table. When your done you can delete all records from the reports table.

[Josh Ormond]

No. Because the dues table is only aware of records in the people table because there is a relationship from a Dues record.  The report, because it's based on the Dues table, is only aware of People through the relationship. If there is no Dues record, FM has no connection to be aware that there is a person to show.

 

For example, I give 1000 marbles.  And tell you to group them by color...and report on number of each color.  The report may look like this:

• Red
  • 200 Marbles
• Blue
  • 400 Marbles
• Green
  • 300 Marbles
• Black
  • 100 Marbles

 

So why don't you list Purple, or White, or Magenta, or any other color?!   Got the idea?

[bruceR]

Ron G: "Absolutely right.  That is how I see it also."

 

That's a hard statement to understand.

There are multiple people commenting here.

Everybody else is consistent in their comments, and has stated pretty clearly that your expectations are wrong.

 

So what is it that you agree with; while at the same time, disagreeing with everybody?

[ron G]

Oh yeah!   I think I get it.  (pounding head on desk).  Thank you for persisting.

 

So, is there a way, other than the import, add blank row etch method previouslsy mentioned, get FM to show all the Dues record for a found set of members EVEN IF THEY HAVE NO DUES?  (I tried basing the LO on Members but it then only picks up the first Dues record in the portal)

 

Thanks again..

 

Ron

[Josh Ormond]

No. Not directly like you are trying to do.  So you have 3 basic options (which can be approached 100 different ways):

• Make sure EVERY member has at least one dues record. (Some conditional formatting tricks could also hide the labels if you just want it to show blank)
• Move the data into another table and use that to "report". Basically, flatten out the data temporarily, just to get the report.
• Virtual List - similar to the 2nd option, but doesn't actually move the data into a table (at least not the way we usually think). Report based on that Virtual List.

[ron G]

I have been looking at ExecuteSQL.  Does anyone think using ExecuteSQL might be a solution for this situation?

[Brooks]

I think there is quite a bit to learn about using SQL, if your not already familiar with it. Which of the previously described methods are you considering using SQL for?

[ron G]

I remember seeing a free 'utility' for helping users understand how to us ExecuteSQL; that is a 'conversion' utility that generates the necessary SQL FM compliant code.   It also sticks in my mind that one may use ExecuteSQL to access unrelated tables in a way that FM (as in this situation) does not allow.... 

 

But then, perhaps I am using 'created memories'...

[Josh Ormond]

ExecuteSQL is a good tool to have. And can reduce relationship dependencies. What it doesn't mean, is that you SHOULD use it everywhere.  The tool you reference will allow you to write SQL code and it transforms it into the syntax that FM uses. It does still require a basic understanding of SQL. Which, I think is fully attainable.

 

The next question is, is ExecuteSQL a good fit for what I am going to use it for? You could use it to gather together all the records you need an display it either in another table by copying the data, or by using Virtual List.  However, since you already have the relationship made, I'm not entirely sure it will help you in this case.  But you can always test a few methods to gather the records you need, and see what performs better for you.

 

And if you are talking about having to edit the data from that report, well, it gets much more complicated to gather, edit, recommit the records back to where they belong ( very possible, but very complicated ).

 

I remember seeing a free 'utility' for helping users understand how to us ExecuteSQL; that is a 'conversion' utility that generates the necessary SQL FM compliant code.   It also sticks in my mind that one may use ExecuteSQL to access unrelated tables in a way that FM (as in this situation) does not allow.... 

 

But then, perhaps I am using 'created memories'...