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

[Tony Diaz]

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]

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]

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]

See if the attached demo helps:

JSON_UpdateOrCreate.fmp12

[Tony Diaz]

That works ..  though I'm not 100% sure why, yet.

Or.. why I shouldn't just do them all that way.
That is, once it clicks with me what is exactly going on there.

(I stepped away from this project for a bit, and am back to it again. )

[comment]

51 minutes ago, Tony Diaz said:

why I shouldn't just do them all that way.

Not sure what you mean by that.

 

[Tony Diaz]

As opposed to the way I have it now. This would be a bit simpler I suppose. I store the text descriptions and titles in a table and then in a separate table, I've got just the ID's in records for each record in the main table that they're associated with. If I just let it store those anyway, using the ID as the key field in that table, it will just update an existing one instead of making a duplicate or having to intercept an error because of a non-unique field entry.

The script excerpt above isn't the greatest example, though the flow is the same, but if I went by Game_ID and Caption, I'd never get duplicates there.

As a consequence, it means I have to collect all those IDs and titles separately and load that that table first and then ignore them during an API call, just taking the ID's for that particular query.

I'm gonna try and work it out, see if I can make sense of it, or post what I run into..