Jump to content

Set field on (multiple) related records


This topic is 6101 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6101 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.