Set field on (multiple) related records

[noneofyo]

Hi all,

I'm relatively newbie to FileMaker, but I'm struggling with it (basically with the ScriptMaker) for a month now.

I've got two tables: products and colours_rel:

Products (Id, ProdName, ...), being Id the Primary Key.

Colours_Rel (FK_ColId, FK_ProdId, ...), being FK_ColId a ForeignKey to a Colour table and FK_ProdId a Foreign Key to Products(Id).

I would like to remove all related records from Colours_Rel and inserting new ones, or at least be able to modify the existant ones. I can read all of it, but only modify the first, because they are not repetitions but related records, so I managed to read them with List:

SetVariable[ $colors; List(Colours_rel::FK_ColId)]

SetVariable[ $i; 1]

Loop

  ExitLoopIf[ $i > Count($colors)]

  ShowCustomDialog[ "debug"; "colour number " & $i & ": " & GetValue($colors; $i)]

  SetVariable[ $i; $i + 1]

EndLoop

I can set the first record with SetField[Colours_Rel::FK_ColId; whatever] but I can't with the others.

Any hint would be really appreciated. Thanks.

[Delta Tango]

If there is a portal on the same page, you need to use "Go to Portal Row [Next]" in order to do this.

You have to go to the portal row (automatically goes to first) and then for each one, go to next.

Otherwise, you have to use go to related records to travel to a layout with these records and then use go to record/page/request next as you make the changes.

When you change things through relationships you can only change the first record.

[Søren Dyhr]

I kind of dislike the use of Go To Portal Row[Next]

Because focus accidentally could be changed by the users clicking, the reasoning behind the answer is fine though.

Set Variable [ $Colors ] 

Set Variable [ $whichRow; Value:1 ] 

Loop 

        Exit Loop If [ $WhichRow>Count ( $Colors ) ] 

        Go to Portal Row [ $whichRow ] [ Select; No dialog ]

        Show Custom Dialog [ Title: "dfsdf"; Buttons: “OK”, “Cancel”; Input #1: plingsportal::Value ] 

        Set Variable [ $whichRow; Value:$whichRow+1 ] 

End Loop 

--sd

[David Jondreau]

I dislike using portals for this too. How about the old Go To Related Record and loop through the found set?

Oh, your profile says you have FM 7, but you're using variables?

Edited October 5, 2007 by Guest

[noneofyo]

I don't know how to specify the portal I want to modify, since there isn't only one portal on the layout (maybe I could try to create a new layout with only the portal I need, will try in a while, first I need to know how to create portals).

Going to related record I think it modifies the current found set, and I should avoid this. I'm updating records from an XML document, which have data from 2 different tables. I can modify the data from the products tables without problem, but the colour table is driving me nuts.

I'm more of a programmer, so maybe that's my problem, as I still don't understand the FM rules. What I would do if I were programming would be:

for (i = 0; i < importedRecords.length; i++)

{

  thisRecord = importedRecords.get(i);

  colorTable.eraseRecordsRelatedTo(thisRecord.Id);

  for (j = 0; j < thisRecord.colors.length; j++)

  {

    colorTable.insertRecord(thisRecord.colors.get(j));

  }

}

I hope you understand this pseudo-code.

So I cannot go to related records, because then I would lose all imported records and it'll be impossible to update their colors, right?

So please tell me if I'm on the correct thinking:

Create an empty layout, create a portal to the color table. Then going to portal row ($i) I should be capable of modifying every field of the color table for this related row? Can I insert new colors through the portal? Can I remove old colors through the portal?

Thank you very much.

ps: I'm using FM 9 Pro over win XP, don't know why it says FM 7.

[bruceR]

Assuming that you have a unique recordID for all records (you always do that, right?) and you have a global that allows you to relate to a selected item record, you can loop through the related records without going anywhere.

Set Variable( $IDList; list( thisRelation::recordID)

Loop

exit loop if isEmpty( $IDList)

set field (yourGlobalField; getValue( $IDList; 1)

commit Record

set field( SelectedRecord::Whatever; something)

set variable( $IDList; middleValues( $IDList; 2; valueCount( $IDList) -1)

End loop