Jump to content

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

Recommended Posts

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" ; "" ; ""  ) ] 
		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?


Link to post
Share on other sites

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.

Link to post
Share on other sites

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.



Link to post
Share on other sites
  • 2 months later...

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. )

Link to post
Share on other sites

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..

Link to post
Share on other sites

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.