Jump to content

Scripting to Create Record Only if Value Does Not Exist?


Recommended Posts

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.

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

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

Link to post
Share on other sites
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.

 

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.

 

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

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

 

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
  • Similar Content

    • By Jim Gill
      Hello All,
      I have a very old script (fmp 10?) that creates User Accounts and assigns Privilege Sets.  The users are then able to Login via WebDirect (primarily).  The Add Account function is used with the following parameters:
      Add Account [ Account Name: _Parent Data::Parent1_Email_TrimAndRemoveReturns; Password: _Parent Data::
      Parent1_WebPassword; Privilege Set: Web_User ]
      [ Expire password ]
      The Password is temporary (for first login only) that is randomly generated via Custom Function.   Users are required to change their password on first login.  
      The account is created but for some reason the user cannot Login via Webdirect or Client (Invalid Account/Password).  I checked the privilege sets and they all check out.  I can manually change the password form the Security Console and require a new password - and everything works as expected.   Again this script worked for years - we typically only create accounts in the fall - so if there has been a major security update or some other new requirement  that would throw a wrench in the works - well, hoping you all can help me out.  
      Thank you in advance!
      Jim
    • By mvoogt
      I have started sending emails using the INSERT FROM URL method shown by soliant here:
      https://www.soliantconsulting.com/blog/html-email-filemaker/
      Emails are being sent out but many recipients seem to be getting my emails sent to their spam folders. I'm curious if this has anything to do with this method of sending emails.
    • By Stickybeak
      I have been running a document creation and management solution for 2 years.
      My scribe maintenance subscription ran out so I bought a new license.
      Boom the whole things has collapsed.
      Scribe is throwing errors when a field in the database does not appear as a merge field in the document the attached being a typical error.
      This solution is vital to my practice but 360Work have no taken 2 working days with no solution - not responding to my emails when I tell them that their work arounds aren't working.
      This was suggested "ScribeSetErrorCapture(true)" - but that does not even appear in my steps in the script window.
      Utterly frustrated.
      Help!

    • By wedgeman
      Not sure if this is the best neighborhood for the question, but it is tied to network/db security.
      What method is used to best determine how a user is logged in? Is Get(MultiUserState) the best method for determination of who/how someone is accessing a locally shared (peer-shared) database?
      For licensing purposes, we run several authorization scripts during login, and it is helpful to know how the access is working.
    • By wedgeman
      Am a bit lost on what (I think) should be a quite simple script:
      Field 1 has a value list on layout.
      Field 2 is a general text field for typed data entry..
      My goal is to use Field 1 to trigger/manage a script which does severla things, but then leaves the mouse/selector inserted into Field 2, for ease of use...
       
      So...
      When I select an item in that value list, an OnObjectExit triggered script: 
      sets a variable from that, then clears that field inserts a calculated value (based on the variable created) in another field.  Then it SHOULD "go to Field 2"... as in, after the script is done, the cursor should be inserted into Field 2 with a blinking cursor... My problem is, this isn't working.
      the first two parts are working flawlessly - it creates and acts on variables, as it should..
      But it refuses to 'go to field' (or "go to object", if i try it that way)..
      What's happening instead is this: the script runs, but the selector/mouse jumps on forward to the next object (if that object is in a tab order), or (if NOT in a tab order), it goes nowhere (it operates the script, but dies before setting mouse to the field or object).....
      I also tried OnObjectModify and OnObjectSave.... No dice on either.....The mouse simply refuses to "go to field"....
      additionally, i tried triggering ANOTHER script (which works on its own) - - simply "go to field 2".. but even that doesn't work..
      thoughts?
       
      My guess is that the script (as an OnObjectExit trigger) runs, then FMP assumes that after the script ends, the next object should be selected (rather than ending wherever I told it to end)...
       
      anybody?
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.