Portal and related records

[sotiris_2]

Hi,

I have a problem and I need some help. I’m a very beginner. I use FM8 on XP for the first time and as usually, it’s painful when you don’t know the software and especially scripting part.

This is my problem. Let say I have one layout with portal, the main table and the related table – it’s in the portal. Now, if I have some records, it’s displayed in the portal. One record in the portal has ID record form another table.

I would like to know:

1) how can I check the number of records in the portal (tab: Class & ScheduleClass) ? One field in the table ScheduleClass is populated from value list based on the table Plage.

2) if there are some records, for every record in the portal I need to get some info from the record: fk_ID_Plage, then go to the related table – Plage, usging the record id = fk_ID_Plage, get the rest of the info I need, do some calculation and finally insert the result into 4th table - CalendarClass

3) Repeat the loop

These are my tables:

Class

ID_Class

Sigle

Group

…

ScheduleClass

ID_ScheduleClass

Fk_ID_Plage

Fk_ID_Class

Plage

ID_Plage

Start

Finish

Day

…

CalendarClass

ID_Calendar

Fk_Class

Date

…

thank you ...

[John Mark Osborne]

1) Sounds like you might want to look at Aggregate functions like Count since they can aggregate the records displaying in a portal.

2) and 3) I'm not sure I understand your question here. Could your explanation another try? You might try explaining what you want to do without using just field and table references. Just describe what you currently have an what you want to happen.

[sotiris_2]

First of all – thank you.

I posted portal’s picture. On the layout you can see 2 tables, tbCours & tbHoraireCours. One record in tbCours can have records in tbHoraiareCours, in that case I need to take the id – the field fk_ID_Plages on the pic, one at the time, for every record (here: 166,119,59) . When I click on the button on the portal, I need to

- get the field fk_ID_Plage, for example 166

- go to the table tbPlages (it’s not on the layout) and find the record 166

- get the info

- calculate the date, time, i.e.

- with the result of the calculation go to the table tbCalendrierCours (it’s not on the layout), make new record and insert the result and the ID_Cours from tbCours

- go back and take the next fk_ID_Plages and do the same

- when there’s no records in the portal, show dialog window

[John Mark Osborne]

You can use the Go to Related Record script step to locate related records. Use the option to "show only related records" so all the records are placed in found set. I would also precede the Go to Related Record step with a Commit Records/Request step so no rows are selected. This will place you on the first record from the portal with a found set of all the records in the portal. This will enable you to follow the Go to Related Record step with a loop. The loop can walk through all the records in the found set using the Go to Record/Request/Page step with the option to go to next record and exit after last record. Also within the loop will be the steps you need to move information from one table to the other. I would suggest the Set Variable step as a good way to transfer information one table to another.

[sotiris_2]

I did something like this but it’s almost worked. When I click on the button I get only the ID from the first record and not the rest. In the case were there’s no records, I show the message “empty”. This way I know there’s nothing to be calculated.

Probably, there’s a problem with loop. I tried different set up but without success.

In the Go to releted record I choose

- show only related records + match all records in ..

Edited June 30, 2006 by Guest

[John Mark Osborne]

I would suggest using the script debugger but it doesn't look like you have FileMaker 8 Advanced. So, I would use the following If statement to disable steps:

If [0]

#steps you want to disable

End If

Start by making sure your Go to Related Record step is working fine. Place the If statement around everything after the Go to Related Record. If this works, move the If statement down so you can disable less steps and test again.

You can also use the Pause/Resume Script step to pause the loop so you can see what is going on. And, you can even use the Show Custom Dialog step to pause and tell you where you are in the script. These steps will slow down the script so you can see where the problem is.

[sotiris_2]

I replaced the statement

go to record with go to portal raw … with the same condition,next, exit after last

It seems to work now …

Now I have to do the rest ...

[sotiris_2]

Hi,

Now I’m able to go trough the portal but there’s always something bothering me. With the first script (script1), I can go trough the records in the portal and get the fk_ID but the final script (script2) have to do more and a new problem for me. The problem is script2. When I get the fk_ID_Plage, I’m able to go to the table “tbPlageHoraire” (Go to Layout) and get the info I need (GetNthRecord), insert into another table and go to the next record in the portal, shortly - repeat the loop. But, when I go to the table tbPlageHoraire, the context change and the loop doesn’t work.It’s stayed on the first record and the loop doesn’t work. Any idea how to keep track of the context, I think that’s the problem, or how to get back to the portal and get correctly next record and repeat the loop??

[Vaughan]

As soon as you change layouts you are losing focus on the record/portal row you're on.

Rather than looping through portal rows, use the Go to Related Records script step and work through the related records. Much easier to do, and harder to lose where you are.