Defining Column Width within SQL Select

["... you mean these fans?"]

Hi,

I need control of my column width.

The following SQL select is perfect with the results I want except I have been unsuccessful in finding a way to set my column width.  All I can do is to set my delimiter by 15 - 20 spaces which pushes the results apart but they are staggered depending on the field data's length.

Filemaker's SQL pdf reference doesn't seem to reference a way to control the "Width" of a column within an SQL statement.

I tried the "Right( )" but it only threw a "?" in the results and I had to remove them for "Quantity and Item_Cost_Plus_MU"

Does anybody have a suggestion.  I would be grateful.

 

Thank you.

 

**********************************

ExecuteSQL ( 

"Select 

A_fk_RFQ_ID,
Item_Name,
Quantity,
Round(Item_Cost_Plus_MU,2)

From Items

Where A_fk_RFQ_ID = ?

" 

; "                         " ; "" ; A_pk_RFQ_ID  )

& "¶" & 

List ( Estimator RFQ ID To Estimator Parent RFQ ID::List Items letter 2 MQ )

[comment]

You cannot "control the width of a column within an SQL statement" because the returned result has no column width property. It's just plain text, expressing tabular data delimited by field and row separators.

To display the data as a table within a text field, you should choose the tab character - Char (9) - as the field separator. Then you'll be able to control the displayed column widths and alignments by adjusting the tab positions and alignments in the target field, using the Inspector in Layout mode (or the text ruler in Browse mode).

---
I should add that you could pad each returned value with a dynamic amount of spaces, using a calculation within the SQL query itself - see the SPACE() function. But then you would have to use monospaced font to display the result.

Edited May 21, 2016 by comment

["... you mean these fans?"]

Hi Comment,

Thank you.

Char (9) work perfectly but the Tab spacing seemed to be ignored.

Filemaker Help does really explain where the issue could be

Please see pic

Any suggestion I would be grateful

[beverly]

Tab stops can be set on the field (in layout mode) and the Char(9) will absolutely be honored in the Text field results for ExecuteSQL(). I do it all the time (including decimal-aligned)! However, you appear to be trying to format only a section of the text. Any tab settings are a part of the entire text returned from the query.

Can you display the full calc for this field?

The other "issue" is that you may be composing an email and any tab stop may NOT be honored when sent from FileMaker. The recipients' email clients may ignore your carefully tab text.

I agree with Comment. You may want to "pad", but even that may not come through as you expect in an email. That being said, here's an article on padding to make "fixed-width" text: https://filemakerhacks.com/2012/08/29/fixed-width-for-edi-and-other-reporting/

beverly

["... you mean these fans?"]

Hi Beverly

Yes Beverly, "Char(9)" is being honored.  It is the "Tab" control that is a blur.

I'm building an editable letter ...

Based on what Comment said using Char(9) in the field separator I've done the following.

This is the field "List Items Letter SQ" that carries the sql ...

ExecuteSQL ( 

"Select 

A_fk_RFQ_ID,
Item_Name,
Quantity,
Round(Item_Cost_Plus_MU,2)

From Items

Where A_fk_RFQ_ID = ?

" 

; Char(9) & Char(9) & Char(9) & Char(9) ; "" ; A_pk_RFQ_ID  )

 

This field calculation that hold the Quote Total "List Items Letter Grand Total SQ"

Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Char(9) & Round ( Sum ( Estimator RFQ ID to Items RFQ ID::Item_Cost_Plus_MU ); 2)

This editable letter comes from a previous link provide from the "Forum" that handle the "Tags" within the letter which was a struggle for me but amazing when I got it.  The orange field is where the customer will "Edit" the text and as you can see, I would love to be able to right align the "Qty" and the "Total" value but I am completely lost with regard to this "Tab" feature recommended by Comment.  It sounds like this "Tab" control will work if I can implement it but it's a blur right now.

 

Thanks 

 

 

Beverly,

This letter, when the customer prints, prints / emails a PDF so I'm good with the email.

Thanks

[comment]

Hard to pinpoint the problem with so many things going on at once - and some only partially described.

For one thing, you should use only a single Char (9) character as the field separator in the ExecuteSQL() function.

For another, you seem to be using not a field, but a text object with merge fields to display the formatted result. Make sure that the tab stops you show are the tab stops set for that text object, and that the paragraph containing the ExecuteSQL result does not have an overriding setting (like most text formats, tab stops can be set individually for each paragraph).

[bruceR]

Yes, use only a single instance of char(9).

See if the attached is helpful.

 

ItemTabs.fmp12.zip

[beverly]

any separator is ok in ExecuteSQL() and if 4 'tabs' (HT) work, there is no problem.  The align on decimal is very powerful! 

I was not sure what "tab control" means, unless it's the tab settings/tab stops for the object as bruce shows you. Are you able to find these dialogs?

beverly

[comment]

3 hours ago, beverly said:

any separator is ok in ExecuteSQL() and if 4 'tabs' (HT) work, there is no problem.

Well, there is a problem, because 4 tabs do not work. If you want to use preset tab stops to display the data as a table in a text field or in a text object (or in a text file, if you're exporting the result), then you must have exactly one tab character separating the fields.

["... you mean these fans?"]

Comment, Bruce R., Beverly

Thank you ... the customer decided not to go with the "Editable Letter". 

So I'm safe for now, I will continue my research to understand how this "Tab" control works now that I have an actual completed layout I can use as a learning tool on a duplicated file.

Being that my results I am looking for stem from a SQL calculation, I am not sure how I would implement BruceR's suggestion.

Anyway, thanks you for all the help.  I am grateful.

:-)

[bruceR]

"Being that my results I am looking for stem from a SQL calculation, I am not sure how I would implement BruceR's suggestion."

Well, that's an odd thing to say. Did you even look at the file? (ItemTabs.fmp12)

The text block in the screenshot IS the result of an SQL calculation.

Edited May 27, 2016 by BruceR

["... you mean these fans?"]

Oops !! missed that :-(

["... you mean these fans?"]

Hi BruceR,

I was able to duplicate your results by creating another field and putting in the "tab" control and it work great.  So I am grateful for that understanding, thank you.

However, the sql field is typed into a template text field where I wrote this letter ... Dear John ... <<fieldName>> etc ... this tag field isn't selectable as a text block, its' just text.

I picked up a function that takes these "tag" fields  and replaces them with their relative data.

I may be missing it completely here but the sql field that is selectable in your demo file ... my sql field since they live in the letter itself are not selectable as a text block.

Again, I don't know, I my have missed your point.

Anyway, see pic of field I push the template too.  I tried placing the "tab" control on this field and when the function replaces those "tag" fields with their respective data the tab control isn't applied since I could never select the sql field itself.

So thank you for the understanding of the "Tab" control.

:-)

[bruceR]

I have no idea what "placing the tab control on the field" means.

It isn't a tab control. It ruler formatting.

["... you mean these fans?"]

BruceR,

Ok ... ruler then.

My point being, your sql field is selectable in your demo to set up the "Ruler", my sql field it is not selectable, meaning it is part of a text document (Letter) that I wrote in a field on my template table and the sql field(s) are merged within the text of my letter field ... Dear <<fieldname>>, and this text field template I use a relationship to set the letter text field on the table where the quote lives

Anyway Bruce, you've guided me in the understanding of this "Ruler" which is more important.  That demo file was excellent, it was what I needed.  So thank you for that :-)

Thank you.

:-)

[bruceR]

The style of the section of the TEMPLATE will be applied to the result.

[comment]

It is very difficult to understand what you're saying. Please refer to the help if you don't know what something is called.

I think what you describe is a text block that contains merge fields. And (as I said earlier) a text block has its own tab setting. If you select the entire block (by clicking on it once), you can then adjust the tab settings for the entire text block. You can do this using the Inspector or the text ruler.

In addition (as I also said earlier) you can set different tab stops for each individual paragraph of the text block. Note that you cannot set tab stops for an individual merge field - but you can set individual tab stops for the paragraph that contains that merge field.

 

Edited May 27, 2016 by comment

["... you mean these fans?"]

Comment,

Ok gotcha  ...

Thank you.

:-)

[beverly]

On May 26, 2016 at 0:56 AM, comment said:

Well, there is a problem, because 4 tabs do not work. If you want to use preset tab stops to display the data as a table in a text field or in a text object (or in a text file, if you're exporting the result), then you must have exactly one tab character separating the fields.

export does NOT require ONE tab. You can have "blank" columns on export: 

a<tab><tab><tab>b

c<tab>d<tab><tab>e

[comment]

3 hours ago, beverly said:

export does NOT require ONE tab. You can have "blank" columns on export: 

And I said:

On 05/26/2016 at 7:56 AM, comment said:

you must have exactly one tab character separating the fields

Even if you have blank columns, you will still have ONE tab character between each column. If you add another tab character, that would signify to the receiving application that there is one more blank column there. Since that column doesn't exist, you wouldn't want to do that!