New invoice for client - how to check if already exists for entered date

[db_tragic]

I have a 'create new invoice' script/button which (using 'set variable' expression to insert current clientnotesID) takes the user to the Invoice layout ready for a new record to be entered.

 

The script finishes with ;

Go to Field [select/perform; Invoice::Date] 

 

.....to begin date entry using calendar wheel (iPad).

 

I want to set the date field up so that when a date is selected, there is a check that the client visit doesn't already have an invoice for that date. (i.e. to stop the user entering another invoice for that particular dated visit.)

 

I envisage a custom dialogue along the lines of:

"An invoice for this client/date already exists. Use the "View Invoice" button to modify." - OK - 

 

The user would then be taken back to the layout "Client Notes" where the 'create new invoice' script/button was initiated, and there is a "View Invoice" button they can then use.

 

What I don't know how to do is the date check/custom dialogue component, and subsequent return to Client Notes layout, combined.

 

TIA

[jbante]

The ExecuteSQL function is a great way to check for any existing invoices:

 

ExecuteSQL ( "SELECT COUNT(*) FROM Invoice WHERE id_Client = ? AND Date = ? and id <> ?" ; "" ; "" ; Invoice::id_Client ; Invoice::Date ; Invoice::id )

 

If the result is greater than 0, you already have another invoice for that client and date. (id_Client is the Invoice's foreign key for the Client record, and id is the Invoice's primary key. I also recommend wrapping field and table names in SQL queries with custom functions so that the query will continue to work as expected if the field names change, like these SQLField and SQLTable functions.)

[LaRetta]

Hard-coding in ExecuteSQL() statements is causing breaks in many solutions I've reviewed. I do not think everyone ( particularly those newer to FM ) realizes that they are working within a text block. Reminding everyone is good idea, thank you. As for reaching for ExecuteSQL(), I still can forget but I'm remembering to consider it more and more and I'm tickled when it eliminates a table occurrence or otherwise simplifies. This is quickly becoming my second-favorite function. :-)