Jump to content

Control Column Widths in ExecuteSql


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

Recommended Posts

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!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by vanderark1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by vanderark1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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!

 

 

Link to comment
Share on other sites

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.

custom tabs.png

ColumnFormats.fmp12

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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 by vanderark1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

 

 

Link to comment
Share on other sites

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 by ggt667
Link to comment
Share on other sites

This topic is 2481 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
×
×
  • Create New...

Important Information

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