I have a button set up where if I click it a checkmark is shown, while on the second click the checkmark disappears. The way I have this set up is with a container field (ButtonField) defined as an Auto-enter calculation set to: GetRepetition ( CheckmarkField ; 1 ). My second field is a global container field with two repetitions. The ButtonField is set up as button that sets its field according the following case function:

 

 

Case (

 

ButtonField = GetRepetition ( CheckmarkField ; 1 );GetRepetition ( CheckmarkField; 2 );

 

GetRepetition (CheckmarkField ; 1 )

 

)

 

This all works fine. But I have a set up inside a portal where I have a list of portal records, each of which has contains the ButtonField. When I check a ButtonField in one record, I want the others to uncheck. I know how to do this when looping the portal but I want to achieve this with SQL based loop. I know how to achieve the looping portion of the script but I'm struggling with the syntax for UPDATING and/or Selecting the repetition of the ButtonField. I've tried the following to no avail. Any ideas?

 

ExecuteSQL (" SELECT RecordID FROM Table WHERE GetRepetition (CheckmarkField) = ? " ; "" ; "¶" ;  1)   

//returns "?"

 

ExecuteSQL (" SELECT RecordID FROM Table WHERE ButtonField = GetRepetition (CheckmarkField) = ? " ; "" ; "¶" ;  1)   

//returns "?"

 

ExecuteSQL (" SELECT GetRepetition(CheckmarkField; ?) FROM Table WHERE RecordID = ? " ; "" ; "¶" ; $$RecordID  ) )

// returns: "?"

 

ExecuteSQL (" SELECT Button_check FROM Table WHERE RecordID = ? " ; "" ; "¶" ; $$RecordID    )

//returns: Untitled

 

ExecuteSQL (" SELECT Icon_check FROM Table WHERE $$RecordID  = ? " ; "" ; "¶" ; $$RecordID      )

//returns: radio3.gif

 

ExecuteSQL (" SELECT RecordID FROM Table WHERE Button_check =  ? " ; "" ; "¶" ;  GetRepetition (CheckmarkField;1) 

//returns: "the specified table cannot be found" in reference to the checkmark field. 

 

I'm also curious about the syntax of that last example. I've seen two ways in which fields have been referenced directly as SQL parameters: (1) Table.Field and (2) "& Field & ". I've looked in a bunch of places regarding this syntax but keep coming up short. Any direction would be appreciated. And THANK YOU GUYS as always.

 

Don't use GetRepetition.

 

The square bracket syntax is the preferred method of addressing repeats.

 

CheckmarkField[9]

 

not:

 

GetRepetition( CheckmarkField; 9)

Hmm, I just plugged the following into the data viewer and it still returned "?". Any idea of what I'm doing wrong?

 

 

ExecuteSQL (" SELECT RecordID FROM Records WHERE Icon_check[2] " ; "" ; ¶ )

 

I also tried this with the same result:

 

ExecuteSQL (" SELECT RecordID FROM Records WHERE Button_check[2] " ; "" ; ¶ )

 

Next, I'll try:

 

ExecuteSQL (" SELECT RecordID FROM Records WHERE Button_Check = Icon_check[2] " ; "" ; ¶ )

 

 

 

You're not writing a complete query.

Think of a query like this:

Show me records where last name = "Smith"

But you are writing

Show me records where last name

Filemaker is still waiting for you to actually ask a question.

I'm going to try and attach a picture just so that I can be explicit as possible. I'm fairly new to SQL and appreciate you guys taking the time to walk me through some of this stuff, which I'm sure to you guys is rudimentary. But I read my query as:

 

"Show me records": SELECT RecordID 

"where last name equals": WHERE Button_check

"= "Smith": Icon_check[2]

A graphic representation of my confusion. 

Figured it out. I don't know why it takes me so long to crack this stuff. Thanks again for the help.

 

ExecuteSQL ( "SELECT kp_ConditionID FROM Conditions WHERE Button_check = ? "; ""; "¶"; Conditions::Icon_check[2] )