GetRepetition equivalent with portals

[Eggshen]

Is there a GetRepetition equivalent for portals in FM 5.5?

I'm trying to get the substitute function to find strings from several related records...

For example, I have a long list of museum artifacts in a regular field called 'Artifacts' in a database named 'Museum'.

I have a portal that contains related records from another database called 'Special' that has the names of each artifact in a field called 'Name.' Based on a relationship between 'Artifacts' and 'Name' in the 'Special' db, I have a portal in 'Museum' that shows all related records of 'Artifacts' that have a corresponding record.

My problem comes from the next part.

I want to have another field that contains a list of all the artifacts in the 'Artifacts' field that ARE NOT in the portal called 'Not'.

One solution I came up with is having the the 'Not' field a calculation field that does:

Substitute('Artifacts', 'relationship::Name',"")

However, this will only substitute the first artifact in the portal. How can I get this to loop or something to go through all the artifacts in the portal?

Is there any other information that would be useful?

[ernst]

You can loop through a portal with the following script lines:

goto portal row (first)

loop

//insert your script steps here//

goto portal row (next, exit after last)

end loop

Three conditions must be met for this script to work in this form:

- a layout containing the portal must be selected whilst this part of the script runs

- allow creation of new records must be unchecked in the relation definition

- show vertical scrollbar must be checked in the portal dialog

Hope this helps,

Ernst

[Eggshen]

Thanks...there wouldn't by any chance be a workaround for when creation of new records IS checked, is there?

Maybe with some sort of Count(relatedfield)...Ill have to experiement.

[-Queue-]

It might be a better idea to loop through the related records, not the portal.

Go to Related Record [show, portalRelationship]

Perform Script [External]

Exit Record/Request

In the external (child) file:

Go to Record/Request/Page [First]

Loop

{do stuff}

Go to Record/Request/Page [Exit after last, Next]

End Loop

[ernst]

I would not call it a workaround, but there are several ways to accomplish this.

You could use the following:

goto portal row (first)

loop

//insert your script steps here//

exit loop if (status(current portalrow) =>Count(RelatedDatabase::SomeField))

goto portal row (next)

end loop

regards,

Ernst

[Eggshen]

Unfortuantely, looping through related records wouldn 't work for my particular purposes (I think).

[ernst]

Hey Queue,

I agree partially... The advantage of looping through the related records like you suggest is that it's easier to do error logging in a multi-user situation where another user is modifying one of the records

The advantage of looping through the portal records is that it keeps the found set in the related database.

In FM7 you could do the looping through the related records in another window. Yes!

Ernst

[ernst]

>>Unfortuantely, looping through related records wouldn 't work for my particular purposes (I think).

Why?

[Eggshen]

Hrm..I'm probably just not quite understanding scripting.

Basically, I want the value of a field to change, which it does in each row.

How do I refer to this value that constantly changes?

As it stands, I'm using doing the following:

Go to Portal Row [select, First]

Loop

Insert Calculated Result [select, "Fieldwhereresultsgo", "Substitute(ListOfEverything, relationship::individualName, "")")

Exit Loop If [stuff you had above]

go to Portal Row [Next]

End Loop

I guess my problem is that I'm not sure if 'relationship::individualName' in my Substitute Function is actually changing with each Portal Row.

So to answer your question Queue, I'm not sure if I'd be able to use the Substitute function to change a field in one database from another database's external script.

[ernst]

>> I guess my problem is that I'm not sure if 'relationship::individualName' in my Substitute Function is

>> actually changing with each Portal Row.

You could check *that* by adding the 'relationship::individualName'-field temporarily to the portal in the layout.

[jfmcel]

I've done something similar by creating a valuelist and then using design functions to parse the values of the valuelist. Ordering was the biggest problem, as if I remember correctly, the value list from a related field comes back in alphabetical order. I had to create a field that had the order (01, 02 ...) and the value concatenated and use the new field in the value list.

[Eggshen]

This value list idea was extremely helpful, and did just what I needed without any sort of loops!

I made a value list of the values in the portal, then used the ValueListItems function inside of the substitue function. It works wonderfully, thanks all who replied!

[Fenton]

Make sure the ValueListItems calculation is Unstored. Else it will appear to work, but will not change.

[jfmcel]

Conversely, the field from the related file the valuelist is based on needs to be indexed -- i.e. it must be stored.