Monkeybread Software - MBS FileMaker Plugin: SQL Select

Components	All	New	MacOS		Windows		Linux		iOS
Examples	Mac & Win		Server	Client	Guides	Statistic	FMM	Blog	Deprecated	Old

MBS FileMaker Plugin Example Databases

SQL Select

All examples are included with download of MBS FileMaker Plugin.

SQL Select.fmp12

Overview
Tables	2
Relationships	0
Layouts	8
Scripts	0
Value Lists	2
Custom Functions	0
Custom Menus	30

File Options
Default custom menu set	[Standard FileMaker Menus]
When opening file
Minimum allowed version	12.0
Login using	Account Name; Account= Admin
Allow user to save password	Off
Require iOS passcode	Off
Switch to layout	Intro
Hide all toolbars	Off
Script triggers
OnFirstWindowOpen	Off
OnLastWindowClose	Off
OnWindowOpen	Off
OnWindowClose	Off
OnFileAVPlayerChange	Off
Thumbnail Settings
Generate Thumbnails	On; Temporary

Tables

Table Name	Statistics	Occurrences in Relationship Graph
SQL Select	9 fields defined, 1 record	SQL Select
ZIP_CODES	7 fields defined, 42741 records	ZIP_CODES

Fields

Table Name: SQL Select - 9 Fields
Field Name	Type	Options	Comments	On Layouts	In Relationships	In Scripts	In Value Lists
zipToLookup	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select
City	Normal, Text	Auto-Enter: Allow editing Context table: SQL Select Calculation: MBS("FM.ExecuteSQL"; "Select City from ZIP_CODES where zip = '" & SQL Select::zipToLookup & "'") Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select
State	Normal, Text	Auto-Enter: Allow editing Context table: SQL Select Calculation: MBS("FM.ExecuteSQL"; "Select State from ZIP_CODES where zip = '" & SQL Select::zipToLookup & "'") Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select
County	Normal, Text	Auto-Enter: Allow editing Context table: SQL Select Calculation: MBS("FM.ExecuteSQL"; "Select County from ZIP_CODES where zip = '" & SQL Select::zipToLookup & "'") Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select
StateToLookUp	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select 2
Column Delimiter	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select 2
Record Delimiter	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select 2
Result	Normal, Text	Auto-Enter: Allow editing Context table: SQL Select Calculation: MBS("FM.ExecuteSQL"; "Select * from ZIP_CODES where State = '" & SQL Select::StateToLookUp & "'"; SQL Select::Column Delimiter; SQL Select::Record Delimiter) Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select 2
Result Copy	Normal, Text	Auto-Enter: Allow editing Context table: SQL Select Calculation: MBS("FM.ExecuteSQL"; "Select * from ZIP_CODES where State LIKE '" & SQL Select::StateToLookUp & "'"; SQL Select::Column Delimiter; SQL Select::Record Delimiter) Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		SQL Select 2

Table Name: ZIP_CODES - 7 Fields
Field Name	Type	Options	Comments	On Layouts	In Relationships	In Scripts	In Value Lists
zip	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: Minimal Automatically create indexes as needed Index Language: English		ZIP_CODES			Zip Codes
latitude	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		ZIP_CODES
longitude	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		ZIP_CODES
City	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		ZIP_CODES
State	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: Minimal Automatically create indexes as needed Index Language: English		ZIP_CODES			States
County	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		ZIP_CODES
Type	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: English		ZIP_CODES

Layout Objects: Intro

Type:

Text: Demo 2

Layout Name:

Intro

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 263 pt
Left: 181 pt
Bottom: 294 pt
Right: 272 pt

Anchoring: Left, Top

Go to Layout [ “SQL Select 2” (SQL Select) ]

Layouts:

SQL Select 2

Button Properties

Coordinates

Script/Script Step

Type:

Text: Demo 1

Layout Name:

Intro

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 263 pt
Left: 91 pt
Bottom: 294 pt
Right: 182 pt

Anchoring: Left, Top

Go to Layout [ “SQL Select” (SQL Select) ]

Layouts:

SQL Select

Layout Objects: SQL Select

Regular Fields

Field Name: SQL Select::zipToLookup

Layout Name:

SQL Select

Top: 256 pt
Left: 99 pt
Bottom: 273 pt
Right: 178 pt

Anchoring: Left, Top

Field Format:

Drop-down List
Display values from: Zip Codes
Auto-complete using value list

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::City

Layout Name:

SQL Select

Top: 306 pt
Left: 93 pt
Bottom: 323 pt
Right: 358 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::State

Layout Name:

SQL Select

Top: 324 pt
Left: 93 pt
Bottom: 341 pt
Right: 358 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::County

Layout Name:

SQL Select

Top: 342 pt
Left: 93 pt
Bottom: 359 pt
Right: 358 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Type:

Text: Demo 2

Layout Name:

SQL Select

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 4 pt
Left: 16 pt
Bottom: 35 pt
Right: 107 pt

Anchoring: Left, Top

Go to Layout [ “SQL Select 2” (SQL Select) ]

Layouts:

SQL Select 2

Layout Objects: SQL Select 2

Regular Fields

Field Name: SQL Select::StateToLookUp

Layout Name:

SQL Select 2

Top: 229 pt
Left: 106 pt
Bottom: 246 pt
Right: 185 pt

Anchoring: Left, Top

Field Format:

Drop-down List
Display values from: States
Auto-complete using value list

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::Column Delimiter

Layout Name:

SQL Select 2

Top: 225 pt
Left: 328 pt
Bottom: 242 pt
Right: 407 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::Record Delimiter

Layout Name:

SQL Select 2

Top: 249 pt
Left: 328 pt
Bottom: 266 pt
Right: 407 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::Result

Layout Name:

SQL Select 2

Top: 301 pt
Left: 12 pt
Bottom: 660 pt
Right: 649 pt

Anchoring: Left, Top

Field Format:

Edit Box
Include vertical scrollbar

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::StateToLookUp

Layout Name:

SQL Select 2

Top: 156 pt
Left: 240 pt
Bottom: 173 pt
Right: 319 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: SQL Select::Result Copy

Layout Name:

SQL Select 2

Top: 139 pt
Left: 696 pt
Bottom: 498 pt
Right: 1333 pt

Anchoring: Left, Top

Field Format:

Edit Box
Include vertical scrollbar

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Type:

Text: Demo 1

Layout Name:

SQL Select 2

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 2 pt
Left: 16 pt
Bottom: 33 pt
Right: 107 pt

Anchoring: Left, Top

Go to Layout [ “SQL Select” (SQL Select) ]

Layouts:

SQL Select

Layout Objects: ZIP_CODES

Regular Fields

Field Name: ZIP_CODES::zip

Layout Name:

ZIP_CODES

Top: 24 pt
Left: 138 pt
Bottom: 41 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: ZIP_CODES::latitude

Layout Name:

ZIP_CODES

Top: 42 pt
Left: 138 pt
Bottom: 59 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: ZIP_CODES::longitude

Layout Name:

ZIP_CODES

Top: 60 pt
Left: 138 pt
Bottom: 77 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: ZIP_CODES::City

Layout Name:

ZIP_CODES

Top: 78 pt
Left: 138 pt
Bottom: 95 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: ZIP_CODES::State

Layout Name:

ZIP_CODES

Top: 96 pt
Left: 138 pt
Bottom: 113 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: ZIP_CODES::County

Layout Name:

ZIP_CODES

Top: 114 pt
Left: 138 pt
Bottom: 131 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: ZIP_CODES::Type

Layout Name:

ZIP_CODES

Top: 132 pt
Left: 138 pt
Bottom: 149 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Value Lists

Value List Name	Source	Values	On Layouts
Zip Codes	Field	Primary Field Field: ZIP_CODES::zip Sort by first field	SQL Select
States	Field	Primary Field Field: ZIP_CODES::State Sort by first field	SQL Select 2

Download example: SQL Select

Used functions:

FM.ExecuteSQL