Jump to content

ExecuteSQL use with Virtual Lists


This topic is 2291 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi all

 

Hopefully this is in the right place, apologies if not.

 

I'm using the technique found in the FM 13 advanced training series to generate virtual lists.

 

I'm using virtual lists to quickly transfer data to a scratch table used for reporting.  I have an ExecuteSQL formula to gather some of the data in question:

ExecuteSQL (

"

SELECT GroupID, SUM(RoundedValue)

FROM DataTable_Home

WHERE  CustID_FK = ? and Code < ? and PeriodNumber = 2 and

(GroupID = 68 or GroupID = 3)

GROUP BY GroupID

";

"|" ; "|" ;

Cust_Home::CustID_PK;

"500"

)

 

This generates in most cases a dataset of:

68|-3600|3|-7200| <<< as an example for this customer with 68 and 3 being the required group 'names' or headers.

 

This data is passed via scripting into a field on the scratch table called ListData which has several fieldsdedicated to particular values, two of which are dedicated to Group68 values and Group3 values.  The formula in each is:

 

Get Value(ScratchTable::ListData;2)<<<For Group 68, -3600

Get Value(ScratchTable::ListData;4)<<<For Group 3. -7200

 

However, there may be cases where a customer does not have any values associated to either 68 or 3 which obviously reduces the return on the dataset and makes the parse section inaccurate.  On my scratch table, I have several fields

 

If a particular customer has NO value for group 68, the returned data set instead looks like:

3|-7200

and this will break all downstream GetValue(Field;x) calculations.

 

So, what I really need to have is a reliable way of ALWAYS returning 4 pipe separated results in the situation that a customer has data missing from one or other of the groups, and that's where I'm stuck.  If I could get to:

 

68|0|3|-7200 or

68|-3600|3|0

as an example, this would enable me to parse the 0 values to where they should be.

 

Any help on this one greatly appreciated.

 

Thanks

Link to comment
Share on other sites

Rather than GetValue, you may have better luck with a Position function that looks for "68|" as the starting point and the next "|" as the end, and extracts the value between them. Be sure to append a "|" to the end of the string first. Search http://www.briandunning.com/filemaker-custom-functions/ for 'extract' or 'parse' or 'between' -- you'll find a fair number of options.

Link to comment
Share on other sites

This topic is 2291 days old. Please don't post here. Open a new topic instead.

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
 Share

×
×
  • Create New...

Important Information

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