Scripting to Create Record Only if Value Does Not Exist?

[Tony Diaz]

I'd like to add a condition to this to not do it if "id" already exists in the table:

 

Set Field [ Moby_Countries_Values::ID ; JSONGetElement ( $record ; "id" ) ]

So that value, 'id' is embedded within the blob that I'm parsing already. In that loop some of the array sets may exist, others many not already.

 

If [ not IsEmpty( $countries ) ] 
	Set Field [ ISBN::JSON_VariableView1 ; $countries ] 
	Set Variable [ $Cover_Group_ID ; Value: ISBN::ISBN_ID ] 
	Set Variable [ $n ; Value: ValueCount ( JSONListKeys ( $authors ; "" ) ) ] 
	Freeze Window
	Go to Layout [ “ISBN_Meta” (ISBN_Meta) ; Animation: None ]	
	Loop
		Set Variable [ $i ; Value: $i + 1 ] 
		Exit Loop If [ $i > $n ] 
		New Record/Request
		Set Field [ Moby_Countries_Meta::Cover_Group_ID ; $Cover_Group_ID ] 
		Set Variable [ $record ; Value: JSONGetElement ( $countries ; $i - 1) ] 
		Set Field [ Moby_Countries_Values::Name ; JSONGetElement ( $record ; "name" ) ] 
		Set Field [ Moby_Countries_Values::Description ; JSONGetElement ( $record ; "desc" ) ] 
		Set Field [ Moby_Countries_Values::ID ; JSONGetElement ( $record ; "id" ) ] 
End Loop
	Go to Layout [ original layout ; Animation: None ]
End If

 

Because afterwards I'll do:

Set Field [ Moby_Countries_Meta::ID ; JSONGetElement ( $record ; "id" ) ]

...and it will relate to the just added / or already now existing record.

[comment]

I can think of number of ways to check if a value already exists in the table. For example, you could set a variable $existingIDs to:

ExecuteSQL ( "SELECT YourIDField FROM YourTable" ; "" ; ""  )

then check if:

IsEmpty ( FilterValues ( $id ;  $existingIDs ) )

Note that this is somewhat simplified. As a rule, you do not want to hardcode your field and table names into an ExecuteSQL() statement, so that you can rename them without breaking the script.

 

 

Edited July 7, 2020 by comment

[Tony Diaz]

So for this example, I want to check against Moby_Screenshots_Meta::Caption

I'm not getting how to wrap this, where to add in an additional loop...  the value I want to check against the FilterValues comes from "JSONGetElement ( $record ; "caption" )", so I need to check it before I do New Record/Request...

Set Variable [ $screenshots ; Value: JSONGetElement ( Items::JSON_Blob_Detail ; "screenshots" ) ] 

If [ not IsEmpty( $screenshots ) ] 
	Set Field [ Items::JSON_VariableView0 ; $screenshots ] 	
	Set Variable [ $PLATFORM_ID ; Value: "5" ] 
	Set Variable [ $MOBY_ID ; Value: Items::Moby_ID ] 
	Set Variable [ $n ; Value: ValueCount ( JSONListKeys ( $screenshots ; "" ) ) ] 
	Freeze Window
	Go to Layout [ “Moby_Screenshots_Meta” (Moby_Screenshots_Meta) ; Animation: None ]
	Loop
		Set Variable [ $i ; Value: $i + 1 ] 
		Exit Loop If [ $i > $n ] 
		Set Variable [ $existingIDs ; Value: ExecuteSQL ( "SELECT Caption FROM Moby_Screenshots_Meta" ; "" ; ""  ) ] 
		// If [ IsEmpty ( FilterValues ( JSONGetElement ( $record ; "caption" ) ; $existingIDs ) ) ] 
		End If
		Loop
			Set Variable [ $record ; Value: JSONGetElement ( $screenshots ; $i - 1) ] 
#           
#			Something here to check "caption" from JSONGetElement ( $record ; "caption" ) against the ListValues result?
#			..and bail if it exists?
#
			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 Loop
	End Loop
	Go to Layout [ original layout ; Animation: None ]
End If

The Filemaker syntax structure seems like, the best way I can relate to it, kind of like RPN vs. Algebraic mode and I'm missing something blatant.

How should this be done properly then?

(..and I get the the issue with the hard coded field names. Which I change the stuff a lot when re-using script bits. So I need to manually review / change these additional things as well.. )

[comment]

14 minutes ago, Tony Diaz said:

# Something here to check "caption" from JSONGetElement ( $record ; "caption" ) against the ListValues result? # ..and bail if it exists?

That something is the If statement that you commented out. It needs to be inside the loop and wrap the entire block that creates and populates a new record.

 

[Tony Diaz]

This seems to work..  I wasn't too far off then. ExecuteSQL is kind of neat

Set Variable [ $screenshots ; Value: JSONGetElement ( Items::JSON_Blob_Detail ; "screenshots" ) ] 
If [ not IsEmpty( $screenshots ) ] 
Set Field [ Items::JSON_VariableView0 ; $screenshots ] 
Set Variable [ $PLATFORM_ID ; Value: "5" ] 
Set Variable [ $MOBY_ID ; Value: Items::Moby_ID ] 
Set Variable [ $n ; Value: ValueCount ( JSONListKeys ( $screenshots ; "" ) ) ] 
Freeze Window
Go to Layout [ “Moby_Screenshots_Meta” (Moby_Screenshots_Meta) ; Animation: None ]
	Loop
	Set Variable [ $i ; Value: $i + 1 ] 
	Exit Loop If [ $i > $n ] 
	Set Variable [ $record ; Value: JSONGetElement ( $screenshots ; $i - 1) ] 
		Loop
		Set Variable [ $existingIDs ; Value: ExecuteSQL ( "SELECT Caption FROM Moby_Screenshots_Meta" ; "" ; ""  ) ] 
		Exit Loop If [ not IsEmpty ( FilterValues ( JSONGetElement ( $record ; "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 Loop
	End Loop
Go to Layout [ original layout ; Animation: None ]
End If

...now the freak'in Insert from URL voodoo. I need to revisit a prior thread. That seems to be a prickly pain.

[comment]

8 minutes ago, Tony Diaz said:

This seems to work.. 

It may work, but it is over-complicated. You don't need an inner loop (what exactly does it loop over?). An If[] condition within the existing loop is all that's required.

The other thing is that the ExecuteSQL() call should be outside of the loop. its result is constant for all iterations of the loop and you are wasting time and resources by forcing it to evaluate $n times instead of once.

 

 

[Tony Diaz]

If I remove that inner loop, when it exits loop if the record exists, it's doing the same thing as the first Exit if $i > $n, and the whole thing quits parsing the rest.

..and if the data being parsed has duplicate values in it, and that $existingIDs doesn't take into consideration anything that has been added during this particular script running.

Thats why I opted for that placement. I do realize it's doing that repetitively otherwise.

If there's a more efficient way, please show me

..and with Set Variable $existingIDs outside of the inner most loop, it's actually failing on the first record it creates for some reason. As in it's trying to duplicate it.

[comment]

You keep talking about exiting the loop, while I am suggesting a simple If[] inside the loop to skip an existing value and continue the loop:

...
Go to Layout [ “Moby_Screenshots_Meta” (Moby_Screenshots_Meta) ; Animation: None ]
Set Variable [ $existingCaptions ; 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 ; $existingCaptions ) ) ] 
		New Record/Request
		Set Field [ Moby_Screenshots_Meta::Caption ; $caption ] 
		# SET OTHER FIELDS IN THIS RECORD
		...
	End If
End Loop
...	
		

 

Edited July 10, 2020 by comment

[Tony Diaz]

Okay,  the fact that IF [ ... ] is doing exactly what I was blatantly missing. Giving me an option to skip ahead. D'oh!

Set Variable [ $screenshots ; Value: JSONGetElement ( Items::JSON_Blob_Detail ; "screenshots" ) ] 
 
If [ not IsEmpty( $screenshots ) ] 
	Set Field [ Items::JSON_VariableView0 ; $screenshots ] 
	Set Variable [ $PLATFORM_ID ; Value: "5" ] 
	Set Variable [ $MOBY_ID ; Value: Items::Moby_ID ] 
	Set Variable [ $n ; Value: ValueCount ( JSONListKeys ( $screenshots ; "" ) ) ] 
	Freeze Window

	Go to Layout [ “Moby_Screenshots_Meta” (Moby_Screenshots_Meta) ; Animation: None ]
	Set Variable [ $existingCaptions ; 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 ; $existingCaptions ) ) ] 
			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