report not showing all records

[kazza]

I've got a file with records listed under diferent dates. In another file i want a report that shows certain fields from each of the records for a month. i've made a relationship matching the month on the report to the month of the records but only the first record is showing up in the report, and its showing up 12 times. Any help?? Please please Please!!!

[trev hatchett]

hi kazza

assuming that your relationship is sound and well-defined, it sounds very much as if you have a field in your portal which is actually unrelated to the portal, even though it may have a name which appears to be correct.

the first thing you need to do is just check that the field name is something like "myPortal::myDateField" where "myPortal" is the name of the relationship that defines the portal.

this type of mixed-up portal is something that i run into many times.

if this is actually at the root of your existing problem and you can solve it by redefining the field correctly, you may quickly come up against another problem. dates can be tricky. for instance, you may find that even though you have used a month in the keys of your relationship, you could easily view all records for "March", say, in the current year, the previous year, the year before that, and so on.

to solve this problem, you would need to match both month AND year if you want to be more precise in your relationship. this is not too difficult in FMP 7 and 8, where you can have multiple keys, so that you can match the current year and current month in a single relationship.

hope this helps

trev

[kazza]

Thanks for replying, only im not using a portal. im just choosing fields from a relationship matching dates. Should i be using a portal? Im kinda confused about what portals show and when i should use them.

[trev hatchett]

OK! Sorry, kazza, I missed that.

You don't actually need a portal.

Without a portal, you have to create a script using the script step "Go To Related Record", making sure you check the "Show only related records" option to limit your found set.

Your aim is to show only those records that match the month you want show. You actually use the same relationship that you would use for a portal in the "Go To Related Record" script step.

The catch is that you also need make sure you start from the layout which shows the records from where the relationship starts. This will be the first step in your script.

Then you need to go to the layout which shows the records from the table that satisfy your relationship, because that is where you can see the records you are looking for. The second step of your script not only finds the records you want, but it also takes you to the layout where you can see those records. You can even open a new window to display that layout.

Your script would be something like this:

Go to Layout [ "Table_1" ; ( Table_1 ) ]

Go to Related Record [ Show only related records ; From table: "Table_2" ; Using Layout: "Table_2_List" ( Table_2 ) ; New window ]

Play with these ideas and I'm sure you'll solve your difficulties.

Good luck

trev

[kazza]

ok that sounds good. But how do i go to a layout in another file to start with? And then how do I specify which record to relate to and choose the month field as the field to relate to?

[trev hatchett]

OK

What you want to do is not trivial because you have to script two files. In fact, a script in one file calls another script in the other file. But don't worry both scripts are very simple.

So you won't get lost, I have created two stripped-down files in FMP 5.5 called "Table_1" and "Table_2", each with a single field called "Month". There is also a relationship that joins the two files "Month" to "Month'. As it happens, both files are almost identical. So to distinguish them, the records in one file are coloured red, and those in the other are coloured blue.

There are two layouts in each file. One layout shows a list of records in the current found set in that file. The other layout shows a single record, and includes a portal to display all of the records [ from the other file ] which are related to the current record on display.

I have put two scripts in each file.

The first script, called "Show me matching records in other file", uses the relationship used in the portal to find only the records you want, hiding all the others. To do this, you need to click the option "Show only related records" for the script step.

This script then goes on to call another script, called "Show selected records in list", in the other file to display the records that have been found there, which is just what you want to do.

This second script is simply used to go to the "List" layout used to view these found records. And in doing so, it also brings the window of the second file to the front.

This is a very straightforward demonstration. And for that reason, there is no error checking, and the relationship does not take the Year into account. It is designed to show you only how a relationship works, and how it can be used to select the records that you want in a dynamic way. You can also see how to call a script in one file from a script in another file. It's quite simple, but powerful stuff.

Hope this helps.

T

[trev hatchett]

sorry, i am having difficulty in attaching and uploading the files

this is a second attempt

trev

found_set_in_second_file.zip