Control Column Widths in ExecuteSql

vanderark1 · June 8, 2017

I have created a "viewer" using a virtual list technique and ExecuteSQL. The "viewer" is a portal that contains one field - a unstored calc text field from the virtual list table.

This allows me to run a script for a "report" and have the results appear in this field. I can run a different script and have different data appear in that field. Everything works great, except column widths.

I understand how to adjust Tabs, alignment, etc using FM>>Line Spacing>>Tabs. However, that sticks to the field regardless of the data it is showing.

I can use hard tabs in the script to place tabs where I want but only for FM fields or text (for column headers for example). I set a dozen tabs in FM>>Line Spacing>>Tabs - one every 50 points and then used Hard Tabs n the script to move column headers (simple text) over as many tabs as desired. This works great.

However, I can't figure out how to control the column width WITHIN the ExecuteSQL function. I need this, as I will end up with dozens of "reports" that I want to view in this single field, all created on the fly via scripting.

Is it possible to control the tab width with the ExecuteSQL function or am I heading down the wrong path?

Thanks in advance for any suggestions!

Wim Decorte · June 8, 2017

Sounds like you are heading down the wrong path. Why not have separate fields in a 'virtual table'? Or use a web viewer to display the data and controlling the spacing through HTML and CSS?

vanderark1 · June 8, 2017

I will look into these suggestions, thanks. It just seems obvious to a beginner (at SQL) that there should be a way to control the column width via the ES code, unless that's outside the scope of ExecuteSQL and can be done in SQL.

Edited June 8, 2017 by vanderark1

Wim Decorte · June 8, 2017

Pretty sure that there is a SQL function that can 'pad' field values so that they always be the same length. If there is such a beast it may not be supported by FM's implementation. But in essence ExecuteSQL() is for retrieving data, not for presenting data.

comment · June 8, 2017

1 hour ago, vanderark1 said:

there should be a way to control the column width via the ES code,

There is no way to control the column width, because the result of ExecuteSQL() does not have a column width attribute. It's just text representing tabular data. It has no display attributes of any kind.

comment · June 9, 2017

12 minutes ago, Wim Decorte said:

Pretty sure that there is a SQL function that can 'pad' field values so that they always be the same length. If there is such a beast it may not be supported by FM's implementation.

Why sure it is:

YourField + SPACE(50 - LENGTH(YourField))

will pad your field with spaces to a fixed width of 50 characters. To make this "smart", you would probably want to use the length of longest value in the field as the starting point instead of a hard-coded number. However, this would only work in a field formatted with a fixed-width font. I would prefer using an HTML table in a web viewer.

vanderark1 · June 9, 2017

After some testing, I think the web viewer is the way to go for my solution. However, I'm having a hard time figuring out exactly what syntax to use in my calculation field that will be placed in the web viewer. Everything I find while searching seems to assume that's already known. This is extremely basic, I know. However, I've never worked with FM's web viewers.

So, I need to put a calc in the web viewer. My calc returns text: html_calc

Assume I want the most basic of basic from W3School:

<!DOCTYPE html>
<html>
<head>
<title>Page Title</title>
</head>
<body>

<h1>This is a Heading</h1>
<p>This is a paragraph.</p>

</body>
</html>

How do I enter that in my field (html_calc) to make it appear properly in the web viewer?

With that info, I can then expand to add the html and css that I want, my data, etc.

To clarify, I can take my global variable and set it in a script that gives me the data I want. The global var is named $$NAV.ITEM.RESULTS

In the web viewer, I put:

"data:text/html,<html><head><title>Data%20URL</title></head><body> " & GetAsCSS ($$NAV.ITEM.RESULTS) & " </body></html>"

This puts my data in the web viewer. But, I need to control the html and css at the calculation, so I never have to change the web viewer, just the calculation, to produce new data.

Edited June 9, 2017 by vanderark1

comment · June 9, 2017

1 hour ago, vanderark1 said:

How do I enter that in my field (html_calc) to make it appear properly in the web viewer?

I don't think that's a good starting point. You have tabular data - and the proper way to display tabular data in HTML is to use a HTML table. Here's the simplest code I could come up with to use in your web viewer:

"data:text/html,<html><body><table border='1'><tr><td>"
&
ExecuteSQL ( "YOUR SQL QUERY GOES HERE" ; "</td><td>" ; "</td></tr><tr><td>"  ) 
&
"</td></tr></table></body></html>"

Of course, that's just a beginning. You will probably want to add headers, style different columns by their data type, and so on. But it does solve the problem of variable-width columns.

Wim Decorte · June 9, 2017

This one has a ton of examples and includes one to show a fully functional sortable table:

http://www.soliantconsulting.com/blog/2016/12/web-viewer-integrations-library

vanderark1 · June 9, 2017

Comment, you very simple code got me going correctly. I need to brush up on HTML and CSS to get it how I want, but it got me over the hump.

Wim, I did look at the specific table in the link to Soliant (found it last night in another post you made). I was pretty lost after looking thru it. But now I think it will start to make sense after work thru the basics of using the web viewer.

Thanks to both you!

bruceR · June 10, 2017

On 6/8/2017 at 0:46 PM, vanderark1 said:

I understand how to adjust Tabs, alignment, etc using FM>>Line Spacing>>Tabs. However, that sticks to the field regardless of the data it is showing.

Not necessarily. You can leave the field unformatted; and use simple templates and substitutions to apply different tab sets.

You can even apply different tab sets to each line.

See attached rudimentary example.

ColumnFormats.fmp12

comment · June 10, 2017

5 hours ago, BruceR said:

You can leave the field unformatted; and use simple templates and substitutions to apply different tab sets.

This is an interesting technique - but it will not adapt itself to data. You can prepare various field widths in advance, but if one of the values exceeds its field's allowed width, that row will be misaligned. A Filemaker's field will wrap the text to its width (it can do even more in Preview/Print); an HTML table cell will try to expand to accommodate the largest value in the column, and wrap the text if it cannot. Even when preparing a fixed-width file export, we chop off the excess characters.

vanderark1 · June 11, 2017

I've been testing quite a bit. Created a text environment with a little over 1,000,000 records. The virtual list returns data to the portal instantly while using a Web Viewer is taking about 7-8 seconds to get the results in the viewer. I feel that I have better control over formatting with the Web Viewer but could ultimately have 5,000,000+ records in my live solution so speed is a factor for sure.

Bruce - thanks for your file. I need to dig in and figure out HOW it works (though I obviously see WHAT it is doing) as it looks to give me much more control over the virtual list field in the portal and may be the way to go. Since I think the web viewer gives me great control over formatting plus the option to use some javascript to add features to the web viewer that I may want, I may still want to go that route. But it may come down to speed.

I'm confused a bit though and I'm sure someone else knows the answer...

I use a script to set a $$GLOBAL. This uses ExecuteSQL.

The unstored calc in the VL table grabs the data from the $$GLOBAL - that data then appears virtually (no pun intended) in the portal whether the returned count is 2 rows or 1000 rows.

The web viewer on the same layout (testing side by side with the portal), uses the $$GLOBAL and it takes 7-8 seconds for the data to appear. Again, regardless of the row count.

I would think that since they are both dependent on what is set in the $$GLOBAL, that they both get that data at the same time and should show the results at the same time. Does it just take the web viewer longer to to show the data for some reason?

Or am I missing something?

Also - to be clear, when I had "just" 300,000 records that the data was coming from, the time difference between the portal and the web viewer wasn't noticeable at all.

comment · June 11, 2017

32 minutes ago, vanderark1 said:

Or am I missing something?

Perhaps I am missing something: why do you need to use SQL at all? With that many records, why don't you perform a proper find and use Filemaker's layout/s to display them?

vanderark1 · June 12, 2017

I'm trying to cut down on the complexity of the Graph. The table with the 1,000,000 records is not related to any other TO in the Graph currently. My reports (that I show in the VL portal and/or web viewer) pull from multiple tables A relationship from the big table to the others isn't needed except for these reports. SQL seems to work very well for that - keeping the work in scripts and not in managing the Graph. I foresee a ton of TO's needed if I try to grab the data needed for the reports.

I'm no expert however, so if SQL is going to be the drag on speed, then I may need to re-think this. But, SQL is putting the records in the single $$GLOBAL and the the VL portal shows them instantly while the Web Viewer does not. It seems logical to me that it's a limitation of the web viewer vs SQL. But, again, not an expert - still have much to learn and very much appreciate your input on this and everywhere on the forums (as I use info found here daily).

Also - this big table has 2,326,000 records - forgot that I doubled up before my most recent testing.

Edited June 12, 2017 by vanderark1

Wim Decorte · June 12, 2017

1 hour ago, vanderark1 said:

I'm no expert however, so if SQL is going to be the drag on speed, then I may need to re-think this. But, SQL is putting the records in the single $$GLOBAL and the the VL portal shows them instantly while the Web Viewer does not

We don't know how you populate the web viewer so we can't comment on its efficiency. Sounds like you're trying to load all records at once, which you shouldn't, use standard pagination techniques to show a subset and then grab the next subset. But I'm just speculating here since I haven't seen your code.

SQL will slow down, based largely on two factors:

- complexity of the query: using any kind of SQL functions or JOINs will exponentially make it slower

- doing the SQL query while you have an uncommitted record in the target table: will force FMS to send you all the data. And with 2+ million records you will certainly feel that pain.

comment · June 12, 2017

I don't really understand what you're doing here, so I am not sure what to advise.

A few random thoughts:

4 hours ago, vanderark1 said:

whether the returned count is 2 rows or 1000 rows.

IMHO, a "report" of 1000 rows is pretty useless. No human can deduce anything useful from it.

4 hours ago, vanderark1 said:

The web viewer on the same layout (testing side by side with the portal), uses the $$GLOBAL and it takes 7-8 seconds for the data to appear.

I don't think that's a valid test. Compare the two methods in isolation, not in parallel - otherwise you don't know what you're testing.

3 hours ago, vanderark1 said:

It seems logical to me that it's a limitation of the web viewer vs SQL.

You are not comparing web viewer against SQL. You are comparing different methods of displaying tabular text data. Once the SQL query has been evaluated, it's just text - it does not matter how it was obtained.

Keep in mind that the web viewer (when displaying the data in HTML table) must find the longest entry in each column before it can start rendering the table. That is the idea behind "controlling column width" which is the main topic here, IIRC.

4 hours ago, vanderark1 said:

it takes 7-8 seconds for the data to appear. Again, regardless of the row count.

That is not my experience. I see the data in the web viewer appear instantly - at least with a reasonable number of rows (I haven't tested otherwise). YMMV if you get too fancy with CSS and/or Javascript.

Finally, do not discount the option of displaying the columns by parsing them out to individual Filemaker fields (or individual repetitions of a Filemaker field).

ggt667 · June 12, 2017

FQL? or SQL?

SQL has this defined as standard: SUBSTR( columnname, 3, 2 ) // Where 3 is starting position and 2 is length of substring.

Edited June 12, 2017 by ggt667

Sign In

Control Column Widths in ExecuteSql

Recommended Posts

vanderark1

Wim Decorte

vanderark1

Wim Decorte

comment

comment

vanderark1

comment

Wim Decorte

vanderark1

bruceR

comment

vanderark1

comment

vanderark1

Wim Decorte

comment

ggt667

Create an account or sign in to comment

Create an account

Sign in

Similar Content

ExecuteSQL query works with values, but not field references

Issue with ValueCount and ExecuteSQL

Creating report with a lot of subsummaries and monthly columns - using executesql queries?

ExecuteSQL fails to return matching record

Completely lost with ExecuteSQL

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information