Depending on the dataset size, you could send the result through a CF like this:
columnStringToNumber ( _list ; _column ; _delimiter )
columnStringToNumber ( _valuelist; _column; ; _delimiter )
by Otmar Kramis, Hochschule Luzern
_list: list returned by the List ( ) - function or EcecuteSQL ( ) with default row delimiter
_column: the column to be processed
_delimiter: column delimiter of the list
Let ( [
n = ValueCount ( _list ) ;
row = Substitute ( GetValue ( _list ; 1 ); _delimiter ; "¶" ) ;
nrow = ValueCount ( row ) ;
value = GetValue ( row ; _column ) ;
newvalue = If ( value = "?" ; 0 ; GetAsNumber ( value ) ) ;
newrow = LeftValues ( row ; _column - 1 ) & newvalue & ¶ & RightValues ( row ; nrow - _column ) ;
newrow = Left ( newrow ; Length ( newrow ) - 1 )
Substitute ( newrow ; ["¶" ; _delimiter] )
Case ( n > 1 ; ¶ & columnStringToNumber ( RightValues ( _list ; n - 1 ) ; _column ; _delimiter ) )
Many thanks Otmar
I can't do this within FileMaker functions, it has to be within the SELECT statement within the ExecuteSQL, which appears to be light on the supported functions we need.
I have got around this by creating an auto calculation field in FileMaker and extracted the value using GetAsNumber and pointing the SELECT to this field instead, but it seems a shame to have to add a field just for this purpose.
Could you not do this in a let()-statement?
Let ( [
_delimiter = Char(9)
; _q = yourQuery
; _list = ExecuteSQL ( _q ; _delimiter ; "" )
; _n = theColimnNumbertoProcess
columnStringToNumber ( _list ; _n ; _delimiter )
Pardon me, Andy. Is the result going into a SQL db or is it the text result from a SQL column from which you are trying to 'extract' part of the value?
Sent from miPhone
Not sure why it "must" be done in a SELECT statement - especially since FMP can't do what you need, apparently, but I would argue that the real issue is that you don't appear to have a database design (a numeric field to hold 1000) that supports your reporting requirements. IOW, you're solving the wrong problem.
Although the database's design might be beyond your control, that would be the first thing I'd fix. In FileMaker you could then create a calculated field (I know you know this already) to hold just the 1000 or whatever number you extract from the "x per annum' field so the table is ready for your SQL.
You could also pre-process the table before doing the SQL if you can't update the database.
Even in MySQL, although I've never had to do this, I would probably just write a stored procedure (that is, if I couldn't fix the "actual" problem -> the database design).
HOPE THIS HELPS.
1 of 1 people found this helpful
I just used ROUND in a SQL statement within FMP, and I believe we use CAST in some of our imports to avoid having to clean up the import. Have you tried CAST on this column?
Edit: Sorry, it appears you will get an error message with text surrounding the number.
Belated Merry Christmas. Good question - this is a text result from an SQL column from which we are trying to extract part of the value. Due to the peculiarities in the way insurers report to their underwriters we've having to generate an 85 column list in a text file on a daily basis that is transferred via SFTP. ExecuteSQL is perfect for this as we can also trap for unwanted line ends/carriage returns by using an unusual row separator, then substitute before outputting.
We're using variations of LEFT, SUBSTR and STRVAL as columns have a finite length due to the system that will be importing the text files and any variations to the specified structure will break the receiving system. We just seem to be short of PATINDEX or similar to achieve our results without resorting to yet another field addition within FileMaker.
But as previously mentioned, this has to be done in the SELECT statement, not using FileMaker functions.
All the best
Just to clarify, we have had to add an additional field within FileMaker just for this report. Up until now, there has been no need to extract any numeric from the source field, it is just a value list that triggers a percentage calculation.
Perhaps we'll be lucky and get a few more SQL functions to play with in the future.
Hope everyone had a good Christmas
I know you stated this;-) But as I read in your reply to Beverly's post, you do post sql-processing anyway (substitute...), so why not this one?
Appreciate your input. The problem is there are 85 columns and this one is buried within something similar to the following extract:
"'', " & //Notes (39)
"'EAP', " & //System Defined product ID (40)
"'', " & //Plan ID (41)
"SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateStart ) & ") ) , 1, 4 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateStart ) & ") ) , 6, 2 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateStart) & ") ) , 9, 2 ), " & //Effective Date (42)
"SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateEnd ) & ") ) , 1, 4 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateEnd ) & ") ) , 6, 2 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateEnd ) & ") ) , 9, 2 ), " & //Terminiation Date (43)
"'BR', " & //Premium Group ID (44) - BROKER AGENCY NO
"'', " & //Product Code (45) - TBA
"'', " & //Premium Group Department ID (46)
84 of the 85 columns can be extracted using SELECT. I do agree I could look for the 2 pipe parameters we're using, which would be pipe 78 and 79 (column 79), and piece together each row using FileMaker functions, but to be honest it is just easier to pop a dedicated field in. It would be so much neater to just use the SQL to generate the data - the functions do exist, but are not supported by FileMaker.
The only post sql processing is that we're not using a pilcrow as a row separator, so we can remove any unwanted line endings and then put the row separators back in, but this is just taking advantage of the ExecuteSQL parameters then using a blanket FileMaker Substitute, not pulling the result apart and reconstructing it.
I agree, the calculation field is a simple solution, I do it this way often. But did you have a look at the CF, iIt takes the column and the column-delimiter as a parameter. the only thing is, it needs the "¶" as row-delimiter. But I assume, you replace the "¶" in values with something else anyway, so it should work, except your result has to many rows and exceeds the max recursion, then you would have to split it.
1. the "FMP15 SQL Reference" may be used with (p. 6):
a. FileMaker database as a data source
b. the ExecuteSQL function
2. the ExecuteSQL() function use a small subset of the capabilities as found in the above guide
3. there are additional SQL calls that may be made on an external SQL source (as connected via ODBC/JDBC) through ESS (approved sources) or Import script step.
4. it's not 'cheating' to use other native FileMaker functions on any data as FileMaker sees it on the Relationship graph (whether imported or ESS - view of SQL table - or native FM table).
Andy, are you using Import or ESS on a SQL database and getting the data into FileMaker and then performing the ExecuteSQL() to extract your data? Because you may have more SQL ability with:
a. one of the FileMaker SQL plug-ins
b. using the Import on an ODBC/JDBC source - using only the SELECT
Really using FileMaker for what FileMaker does extremely well with its functions is going to be far better than relying on one function - ExecuteSQL().
No, we're just needing to extract a whole bunch of info out of FileMaker. These days we're focussing on performance for day to day use and then try to generate reports dynamically to remove the need for summary fields, cross table calculations, additional TO groups, dedicated report layouts, etc. as much as possible. Our view is that if you have n no. of users entering data (in this case multiple countries, currencies, taxes), then their productivity is reduced by n x the additional time it takes to update fields only used within reports.
We tend to build management information reports using dedicated tables, creating records and populating fields using scripted calculations on demand as this is usually only one person at a time and people tend to be more tolerant waiting for complex reports to be presented, rather than when entering data or performing simple finds. FileMaker's inability to Save as Excel by honouring the hide and off screen objects further reduces the ability to generate generic reports (Export has to be defined each time).
I suspect we've previously used just about every trick in the book, as the systems we write often seem to 'push FileMaker's envelope', but in this particular case, we had to resort to a dedicated field, as ExecuteSQL did 99% of what we wanted, that allowed us to set a text field to a variable result and export it for the required purpose. The alternative would have required many of the things above that we try to avoid.
Thanks for the input