Tony Diaz 0 Posted July 24, 2020 Share Posted July 24, 2020 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? Link to post Share on other sites
comment 1,775 Posted July 24, 2020 Share Posted July 24, 2020 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
Tony Diaz 0 Posted July 24, 2020 Author Share Posted July 24, 2020 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
comment 1,775 Posted July 24, 2020 Share Posted July 24, 2020 See if the attached demo helps: JSON_UpdateOrCreate.fmp12 1 Link to post Share on other sites
Tony Diaz 0 Posted October 12, 2020 Author Share Posted October 12, 2020 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
comment 1,775 Posted October 12, 2020 Share Posted October 12, 2020 51 minutes ago, Tony Diaz said: why I shouldn't just do them all that way. Not sure what you mean by that. Link to post Share on other sites
Tony Diaz 0 Posted October 12, 2020 Author Share Posted October 12, 2020 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
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now