Update Existing Records while Parsing JSON vs. Creating New Records?

[Tony Diaz 0]

I've got this to skip existing records when parsing incoming JSON..

If [ not IsEmpty( $screenshots ) ] 
	Set Field [ Moby_Games_Importer::JSON_VariableView0 ; $screenshots ] 
	Set Variable [ $PLATFORM_ID ; Value: "5" ] 
	Set Variable [ $MOBY_ID ; Value: Moby_Games_Importer::Game_ID ] 
	Set Variable [ $n ; Value: ValueCount ( JSONListKeys ( $screenshots ; "" ) ) ] 
	Freeze Window
 
	Go to Layout [ “Moby_Screenshots_Meta” (Moby_Screenshots_Meta) ; Animation: None ]
	Set Variable [ $existingIDs ; Value: ExecuteSQL ( "SELECT Caption FROM Moby_Screenshots_Meta" ; "" ; ""  ) ] 
	Loop
		Set Variable [ $i ; Value: $i + 1 ] 
		Exit Loop If [ $i > $n ] 
		Set Variable [ $record ; Value: JSONGetElement ( $screenshots ; $i - 1) ] 
		Set Variable [ $caption ; Value: JSONGetElement ( $record ; "caption") ] 
		If [ IsEmpty ( FilterValues ( $caption ; $existingIDs ) ) ] 
			New Record/Request
			Set Field [ Moby_Screenshots_Meta::Platform_ID ; $PLATFORM_ID ] 
			Set Field [ Moby_Screenshots_Meta::Moby_IDfk ; $MOBY_ID ] 
			Set Field [ Moby_Screenshots_Meta::Caption ; JSONGetElement ( $record ; "caption" ) ] 
			Set Field [ Moby_Screenshots_Meta::Image_URL ; JSONGetElement ( $record ; "image" ) ] 
			Set Field [ Moby_Screenshots_Meta::Thumbnail_URL ; JSONGetElement ( $record ; "thumbnail_image" ) ] 
			Set Field [ Moby_Screenshots_Meta::Thumbnail_URL ; JSONGetElement ( $record ; "thumbnail_image" ) ] 
			Set Variable [ $redirectURL ; Value: Moby_Screenshots_Meta::Image_URL ] 
			Insert from URL [ Select ; With dialog: Off ; Target: Moby_Screenshots_Meta::Screenshots ; $redirectURL ] 
			Set Variable [ $redirectURL ; Value: Moby_Screenshots_Meta::Thumbnail_URL ] 
			Insert from URL [ Select ; With dialog: Off ; Target: Moby_Screenshots_Meta::Thumbnails ; $redirectURL ] 
		End If
	End Loop
	Go to Layout [ original layout ; Animation: None ]
End If

 

.. but if I want to update existing records?

Would that need to be a search for each record, or is there a mode where it can act like it's using the import / update function?

 

[comment 1,742]

You can update existing records through a relationship. Start by setting a global field to the ID of the current JSON record (I believe that's $caption in your example?). This will establish a relationship to a record with the same ID, if one exists. If you allow the relationship to automatically create records, you can update existing records AND create new ones within the same loop. Of course, you will need to be on a layout of a different TO.

[Tony Diaz 0]

A global field ... in the table that is on the layout where the target records are I gather, before the loop happens?

            Set Field [ Moby_Screenshots_Meta::GlobalField ; JSONGetElement ( $record ; "caption" ) ]

..and something instead of the New Record / Request?

I'm not having too much success with finding examples of scripting / updating existing records.

I'm having a mental block with it at the moment.. hopefully it will come to me.

 

 

[comment 1,742]

See if the attached demo helps:

JSON_UpdateOrCreate.fmp12