script to make relation between two tables - Childsoldiers will be thankful

[smichiel]

As I'm new in FM and this forum, I do have a question about a certain script.

In my DB I do have three tables:

"children", "sponsors" and "adoption".

The children in my DB have a status "free" or "occupied" and an ID.

My sponsors have an ID

In the table adoption I'd like to link a child to a sponsor.

I'd like to make a script that selects automatically a child that is "free", with the lowest "id". This value (child ID) should be placed in the tabled adoption.

Can anyone help me for this script?

Thanks

[Søren Dyhr]

The scripting as such, is next to nothing a single line of Set Field[ and then a Commit Record step.

The tricky part is the relational bit and calc'field used as filter needs to be returning text, and the match field too.

List ( All_Adoptions::fk_child ) & "¶~"

To be on the safe side then substitute the Commit with yet another Set Field[ setting the former calc'field "UsedIDs" as now a global text field.

Take a look at the template attached!

--sd

childsoldiers.zip

[smichiel]

The script included doesn't work I think ("Function missing") and I don't get this picture...

[Vaughan]

The script uses the List() command: check your version of FMP. Is it 8.0 or 8.5?

[Søren Dyhr]

Sorry 'bout that; use ValuelistItems( instead! But Vaughan has a point here!

http://www.filemaker.com/help/Functions%20Ref45.html

Which makes the cartesian relation redundant!

--sd