Custom function to make list of multiple related fields

classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Custom function to make list of multiple related fields

debt
        I'm looking for a custom function that will grab multiple fields from all related records.  It will be used to get certain fields from an invoice, like quantity, item number, and description, and make a return delimited list that I can copy and paste, like so:

3 / #3428   Large widget
7 / #77G-NC Small widget
5 / #AGM    Accessory
2 / #9H7    Link

        Does anyone know of one?  I've searched the custom function site, but the only one's I've found will either return only a single field from all related records, or multiple fields from a single record - but none that do both.

Thank you,
Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Bob Patin
Debbie,

An easy way to do this:

1. In the related table, create a field that concatenates the fields, something like this:

3 — #3428 — Large widget

2. Using a simple calc, you can get the whole list, like this:

myList (calc field, text) = list ( relatedTable::concatenated_field )


Bob Patin
Longterm Solutions
[hidden email]
615-333-6858
FileMaker 9, 10, 11, 12 & 13 Certified Developer
http://www.longtermsolutions.com
-
iChat: [hidden email]
Twitter: bobpatin

FileMaker Consulting
FileMaker Hosting for all versions of FileMaker
PHP • Full email services • Free DNS hosting • Colocation • Consulting

> On May 28, 2017, at 1:26 PM, debt <[hidden email]> wrote:
>
> I'm looking for a custom function that will grab multiple fields from all related records.  It will be used to get certain fields from an invoice, like quantity, item number, and description, and make a return delimited list that I can copy and paste, like so:
>
> 3 / #3428   Large widget
> 7 / #77G-NC Small widget
> 5 / #AGM    Accessory
> 2 / #9H7    Link
>
> Does anyone know of one?  I've searched the custom function site, but the only one's I've found will either return only a single field from all related records, or multiple fields from a single record - but none that do both.
>
> Thank you,
> Debbie
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Beverly Voth-3
In reply to this post by debt
If you are looking for "fixed-width", I wrote an article that may help:
https://filemakerhacks.com/2012/08/29/fixed-width-for-edi-and-other-reporting/
Beverly

> On May 28, 2017, at 2:26 PM, debt <[hidden email]> wrote:
>
> I'm looking for a custom function that will grab multiple fields from all related records.  It will be used to get certain fields from an invoice, like quantity, item number, and description, and make a return delimited list that I can copy and paste, like so:
>
> 3 / #3428   Large widget
> 7 / #77G-NC Small widget
> 5 / #AGM    Accessory
> 2 / #9H7    Link
>
> Does anyone know of one?  I've searched the custom function site, but the only one's I've found will either return only a single field from all related records, or multiple fields from a single record - but none that do both.
>
> Thank you,
> Debbie
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

debt
> On May 28, 2017, at 3:07 PM, BEVERLY VOTH <[hidden email]> wrote:
>
> If you are looking for "fixed-width",

        No, I just wanted the email to look pretty. :)  I only need the ability to pull multiple fields from related records.

> I wrote an article that may help:
> https://filemakerhacks.com/2012/08/29/fixed-width-for-edi-and-other-reporting/

        I'll check it out.  Thanks!

Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Bob Patin
You can also use a tab or two between entries instead of hyphens.

Bob Patin
Longterm Solutions
[hidden email]
615-333-6858
FileMaker 9, 10, 11, 12 & 13 Certified Developer
http://www.longtermsolutions.com
-
iChat: [hidden email]
Twitter: bobpatin

FileMaker Consulting
FileMaker Hosting for all versions of FileMaker
PHP • Full email services • Free DNS hosting • Colocation • Consulting

> On May 28, 2017, at 5:17 PM, debt <[hidden email]> wrote:
>
>> On May 28, 2017, at 3:07 PM, BEVERLY VOTH <[hidden email]> wrote:
>>
>> If you are looking for "fixed-width",
>
> No, I just wanted the email to look pretty. :)  I only need the ability to pull multiple fields from related records.
>
>> I wrote an article that may help:
>> https://filemakerhacks.com/2012/08/29/fixed-width-for-edi-and-other-reporting/
>
> I'll check it out.  Thanks!
>
> Debbie
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

John Weinshel
In reply to this post by debt
If you are using a script, rather than a field definition, it might be
easier and add less weight to your schema to use SQL, particularly if the
'related' part is currently expressed in one TO, and there are thus no
inner joins.

On 5/28/17, 3:17 PM, "debt" <[hidden email]> wrote:

>> On May 28, 2017, at 3:07 PM, BEVERLY VOTH <[hidden email]> wrote:
>>
>> If you are looking for "fixed-width",
>
> No, I just wanted the email to look pretty. :)  I only need the ability
>to pull multiple fields from related records.
>
>> I wrote an article that may help:
>>
>>https://filemakerhacks.com/2012/08/29/fixed-width-for-edi-and-other-repor
>>ting/
>
> I'll check it out.  Thanks!
>
>Debbie
>_______________________________________________
>FMPexperts mailing list
>[hidden email]
>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

debt
> On May 28, 2017, at 3:36 PM, John Weinshel <[hidden email]> wrote:
>
> If you are using a script...

Hi John,

        I wasn't, but that's probably a better idea than a calc in the long run, as the number of items can change in the line items file (it's actually a quotes table, not invoices).  So I wrote a script that grabs the information I need.  

        Thanks for the nudge in another direction.

Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Jonathan Fletcher-2
In reply to this post by John Weinshel
John’s right. If you know a little SQL this is a piece of cake, and is quite powerful.

Beverly even has a great article about it: https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

In your case you will need to know what criteria to filter on. Say, you want to have all the line items from a particular invoice with tabs between the fields, here is one approach:

Let ( [
        $invoiceKey = invoice::ID ; // Save the invoice’s primary key

        sqlQuery = "SELECT quantity, itemNumber, description // field names separated by commas
                                FROM <invoiceLineItemsTableName> // the, um, name of your line item table
                                WHERE <invoiceLineItemsParentKeyField> = ?” ; // “?” is a parameter that will get the contents of arg1.

        fieldSeparator = char ( 9 ) ; // a tab here, but can be any character(s) you want
        rowSeparator = "” ; // defaults to a carriage return

        arg1 = $invoiceKey ;

] ;

        ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator ; arg1 ) // where it all comes together

)


This will give you a nice array of all the data, and as a side-benefit, will paste nicely into a spreadsheet. There are other things you can do to make this calculation more robust, but this will work for you after you substitute the table and field names with your own.

Jonathan


> On May 28, 2017, at 6:36 PM, John Weinshel <[hidden email]> wrote:
>
> If you are using a script, rather than a field definition, it might be
> easier and add less weight to your schema to use SQL, particularly if the
> 'related' part is currently expressed in one TO, and there are thus no
> inner joins.
>
> On 5/28/17, 3:17 PM, "debt" <[hidden email]> wrote:
>
>>> On May 28, 2017, at 3:07 PM, BEVERLY VOTH <[hidden email]> wrote:
>>>
>>> If you are looking for "fixed-width",
>>
>> No, I just wanted the email to look pretty. :)  I only need the ability
>> to pull multiple fields from related records.
>>
>>> I wrote an article that may help:
>>>
>>> https://filemakerhacks.com/2012/08/29/fixed-width-for-edi-and-other-repor
>>> ting/
>>
>> I'll check it out.  Thanks!
>>
>> Debbie
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Jonathan Fletcher
[hidden email]

Kentuckiana FileMaker Developers Group
Next Meeting: 6/27/17

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Michael Kupietz
In reply to this post by debt
I have a custom function mkConcatColumns(list1, list2, delimeter) that
will concatenate two value lists horizontally. This way you can take
list() functions on single fields and concatenate corresponding values,
so each row of the resulting list contains multiple values separated by
a user-specified delimiter.

For instance, if you have four records with three fields like so:

field1, field2, field3
-- -- --
a, b, c
1, 2, 3
red, blue, green
east, west, north

then

mkConcatColumns(
   mkConcatColumns(
     list(field1),
     list(field2),
     " -- "),
   list(field3),
   " / ")

Will produce this text result:

"a -- b / c¶
1 -- 2 / 3¶
red -- blue / green¶
east -- west / north"

The function definition:

Let (
    $mkReservedCounterMCC = $mkReservedCounterMCC + 1 ;
    If (
       $mkReservedCounterMCC≤ValueCount ( Valuelist1 ) or
$mkReservedCounterMCC≤ValueCount ( Valuelist2 ) ;
       GetValue ( ValueList1 ; $mkReservedCounterMCC ) & Delimiter &
GetValue ( ValueList2 ; $mkReservedCounterMCC ) &
          Let ( Next = mkConcatColumns ( ValueList1 ; ValueList2 ;
Delimiter ) ; If ( Next≠ "" ; "¶" & Next ; "" ) );
       Let ( $mkReservedCounterMCC = 0 ; "" )
    )
)


It does use a private variable called $mkReservedCounterMCC, so if you
use it, don't use that variable in your solution.

Mike

--
Michael E. Kupietz
IT consulting solutions for FileMaker Pro • Web • OS X
https://www.kupietz.com
415.545.8743


On 28 May 2017, at 11:26, debt wrote:

> I'm looking for a custom function that will grab multiple fields from
> all related records.  It will be used to get certain fields from an
> invoice, like quantity, item number, and description, and make a
> return delimited list that I can copy and paste, like so:
>
> 3 / #3428   Large widget
> 7 / #77G-NC Small widget
> 5 / #AGM    Accessory
> 2 / #9H7    Link
>
> Does anyone know of one?  I've searched the custom function site, but
> the only one's I've found will either return only a single field from
> all related records, or multiple fields from a single record - but
> none that do both.
>
> Thank you,
> Debbie
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Michael Kupietz
Sorry, I didn't keep the parameter names consistent. To be clear, for
the function definition I gave, the parameters should be
mkConcatColumns(valueList1, valueList2, delimeter).

I've uploaded it to Brian Dunning's site. Don't have an URL because it's
not live yet, but it should be there soon under mkConcatColumns.

Mike

--
Michael E. Kupietz
IT consulting solutions for FileMaker Pro • Web • OS X
https://www.kupietz.com
415.545.8743


On 28 May 2017, at 22:56, Michael Kupietz wrote:

> I have a custom function mkConcatColumns(list1, list2, delimeter) that
> will concatenate two value lists horizontally. This way you can take
> list() functions on single fields and concatenate corresponding
> values, so each row of the resulting list contains multiple values
> separated by a user-specified delimiter.
>
> For instance, if you have four records with three fields like so:
>
> field1, field2, field3
> -- -- --
> a, b, c
> 1, 2, 3
> red, blue, green
> east, west, north
>
> then
>
> mkConcatColumns(
>   mkConcatColumns(
>     list(field1),
>     list(field2),
>     " -- "),
>   list(field3),
>   " / ")
>
> Will produce this text result:
>
> "a -- b / c¶
> 1 -- 2 / 3¶
> red -- blue / green¶
> east -- west / north"
>
> The function definition:
>
> Let (
>    $mkReservedCounterMCC = $mkReservedCounterMCC + 1 ;
>    If (
>       $mkReservedCounterMCC≤ValueCount ( Valuelist1 ) or
> $mkReservedCounterMCC≤ValueCount ( Valuelist2 ) ;
>       GetValue ( ValueList1 ; $mkReservedCounterMCC ) & Delimiter &
> GetValue ( ValueList2 ; $mkReservedCounterMCC ) &
>          Let ( Next = mkConcatColumns ( ValueList1 ; ValueList2 ;
> Delimiter ) ; If ( Next≠ "" ; "¶" & Next ; "" ) );
>       Let ( $mkReservedCounterMCC = 0 ; "" )
>    )
> )
>
>
> It does use a private variable called $mkReservedCounterMCC, so if you
> use it, don't use that variable in your solution.
>
> Mike
>
> --
> Michael E. Kupietz
> IT consulting solutions for FileMaker Pro • Web • OS X
> https://www.kupietz.com
> 415.545.8743
>
>
> On 28 May 2017, at 11:26, debt wrote:
>
>> I'm looking for a custom function that will grab multiple fields
>> from all related records.  It will be used to get certain fields from
>> an invoice, like quantity, item number, and description, and make a
>> return delimited list that I can copy and paste, like so:
>>
>> 3 / #3428   Large widget
>> 7 / #77G-NC Small widget
>> 5 / #AGM    Accessory
>> 2 / #9H7    Link
>>
>> Does anyone know of one?  I've searched the custom function site,
>> but the only one's I've found will either return only a single field
>> from all related records, or multiple fields from a single record -
>> but none that do both.
>>
>> Thank you,
>> Debbie
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

debt
In reply to this post by Jonathan Fletcher-2
> On May 28, 2017, at 6:18 PM, Jonathan Fletcher <[hidden email]> wrote:
>
> here is one approach:

Hi Jonathan,

        I really like the idea of using ExecuteSQL, but I can't get it to work - not even with a simple example such as:

ExecuteSQL ( "SELECT *
              FROM  "Quotes"
              LIMIT 2" ; " -- " ; "" )

        Entering that into the Data Viewer, it tells me that the table can't be found.  "Quotes" is not only the name of the table, but it's the name of the file as well.  What more is it looking for?

Thank you,
Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Jonathan Fletcher-2
Lose the quote marks around the table name and see if that works, Debbie.


> On May 29, 2017, at 12:05 PM, debt <[hidden email]> wrote:
>
>> On May 28, 2017, at 6:18 PM, Jonathan Fletcher <[hidden email]> wrote:
>>
>> here is one approach:
>
> Hi Jonathan,
>
> I really like the idea of using ExecuteSQL, but I can't get it to work - not even with a simple example such as:
>
> ExecuteSQL ( "SELECT *
>              FROM  "Quotes"
>              LIMIT 2" ; " -- " ; "" )
>
> Entering that into the Data Viewer, it tells me that the table can't be found.  "Quotes" is not only the name of the table, but it's the name of the file as well.  What more is it looking for?
>
> Thank you,
> Debbie
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Jonathan Fletcher
[hidden email]

Kentuckiana FileMaker Developers Group
Next Meeting: 6/27/17

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

John Weinshel
In reply to this post by debt
Debbie, there is no 'LIMIT' in Filemaker SQL. If you want to constrain the
results to the first 2 found rows, use "FETCH FIRST [n] ROWS ONLY".

Also, the double quotes right before your closing parens make no sense;
nor does the semi-colon before them. An ExecuteSQL() statement that has no
internal variable (i.e., a question mark within the SQL expression), ends
as mine below-- the SQL expression in:

double quotes,
then a semi-colon,
2 double quotes,
semi-colon,
2 double quotes.

The word 'quotes' is not reserved. As Jonathan points out, it should not
be quoted.


ExecuteSQL
(
"
SELECT *
FROM  Quotes
FETCH FIRST 2 ROWS ONLY
"
;
"";""
)



On 5/29/17, 9:05 AM, "debt" <[hidden email]> wrote:

>> On May 28, 2017, at 6:18 PM, Jonathan Fletcher <[hidden email]>
>>wrote:
>>
>> here is one approach:
>
>Hi Jonathan,
>
> I really like the idea of using ExecuteSQL, but I can't get it to work -
>not even with a simple example such as:
>
>ExecuteSQL ( "SELECT *
>              FROM  "Quotes"
>              LIMIT 2" ; " -- " ; "" )
>
> Entering that into the Data Viewer, it tells me that the table can't be
>found.  "Quotes" is not only the name of the table, but it's the name of
>the file as well.  What more is it looking for?
>
>Thank you,
>Debbie
>_______________________________________________
>FMPexperts mailing list
>[hidden email]
>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

debt
> On May 29, 2017, at 9:24 AM, John Weinshel <[hidden email]> wrote:
>
> Debbie, there is no 'LIMIT' in Filemaker SQL. If you want to constrain the
> results to the first 2 found rows, use "FETCH FIRST [n] ROWS ONLY".

        O.K.  That was the problem.  I got it to work.  Thank you.

        Now, can I grab fields from a related table, like so?  

ExecuteSQL ( "SELECT Q_LINE_ITEMS|quote_ID::Qty, Q_LINE_ITEMS|quote_ID::Item_ID, Q_LINE_ITEMS|quote_ID::Description

              FROM  Q_LINE_ITEMS|quote_ID::Line_Items_Q

              FETCH FIRST 2 ROWS ONLY" ; " -- " ; "¶" )

        This just returns a "?".

Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

John Weinshel
Filemaker SQL ignores Filemaker's relationships, and is only interested in
the base tables.

Within the SQL statement (the part inside the first set of double quotes,
starting with 'SELECT'), you can only use the not-fully-qualified field
name, i.e., 'Item_ID', not 'Q_LINE_ITEMS|quote_ID::Item_ID'. I am assuming
'Q_LINE_ITEMS|quote_ID' is the name of a TO ('table occurrence'), and
'Q_LINE_ITEMS' id the name of the base table.

The FROM clause should not use a column; it just wants the base table name.

Next, your statement has no WHERE clause, so it doesn't have any selection
criteria. Let's say you are in the Quotes table and you want those 3
columns from the line items table. The WHERE criterion is the matching
keys. If the Quotes table's primary key is 'id' and the line items table's
foreign key is'quote_id'

ExecuteSQL
(
"
SELECT Qty, Item_ID, Description
FROM Q_LINE_ITEMS
WHERE quote_id = ?
FETCH FIRST 2 ROWS ONLY
"
;
"--"; ""; Quotes::id
)



The missing WHERE clause is the line 'WHERE quote_id = ?', where the
question mark stands in for the primary key in Quotes.

Without the FROM clause, the 3 columns from every line item will be
returned.

That primary key is placed after the last required semi-colon (where you
have the paragraph sign), and it is fully-qualified: it includes the table
and column names, separated by a double colon, i.e., 'Quotes::id'.

Note you do not need that paragraph sign for the row separator, which is a
return by default.

Hope that gets you closer. Filemaker SQL is maddening because it offers no
formatting, and doesn't give you a clue about your error(s), just that
question mark.

Try it without the WHERE clause first, to get started:



ExecuteSQL
(
"
SELECT Qty, Item_ID, Description
FROM Q_LINE_ITEMS

FETCH FIRST 2 ROWS ONLY
"
;
"--"; ""
)




On 5/29/17, 10:43 AM, "debt" <[hidden email]> wrote:

>> On May 29, 2017, at 9:24 AM, John Weinshel <[hidden email]> wrote:
>>
>> Debbie, there is no 'LIMIT' in Filemaker SQL. If you want to constrain
>>the
>> results to the first 2 found rows, use "FETCH FIRST [n] ROWS ONLY".
>
> O.K.  That was the problem.  I got it to work.  Thank you.
>
> Now, can I grab fields from a related table, like so?
>
>ExecuteSQL ( "SELECT Q_LINE_ITEMS|quote_ID::Qty,
>Q_LINE_ITEMS|quote_ID::Item_ID, Q_LINE_ITEMS|quote_ID::Description
>
>              FROM  Q_LINE_ITEMS|quote_ID::Line_Items_Q
>
>              FETCH FIRST 2 ROWS ONLY" ; " -- " ; "¶" )
>
> This just returns a "?".
>
>Debbie
>_______________________________________________
>FMPexperts mailing list
>[hidden email]
>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Beverly Voth-3
A table occurrence (table alias name ON the graph) must be used in ExecuteSQL queries. But existing _relationships_ do get ignored and any criteria of the relationship must be recreated (if you need it) in the query (likely with a JOIN). So, the base table name is not used, but all fields/data in it is available to the query.
Beverly

Sent from miPhone

> On May 29, 2017, at 2:14 PM, John Weinshel <[hidden email]> wrote:
>
> Filemaker SQL ignores Filemaker's relationships, and is only interested in
> the base tables.
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

debt
> On May 29, 2017, at 11:25 AM, Beverly Voth <[hidden email]> wrote:
>
> A table occurrence (table alias name ON the graph) must be used in ExecuteSQL queries.

Beverly,

        What is a "table alias name"?  I see a Source Table and Data Source on the graph but not a table alias.  Also is "ON" an acronym like "TO", or did you just capitalize it for emphasis?

Thanks,
Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

Beverly Voth-3
The occurrence is an alias name for those who use  SQL (in other databases) and understand its usage. Thus it was in parentheses. The ON was emphasis, mine.

Sent from miPhone

On May 29, 2017, at 2:44 PM, debt <[hidden email]> wrote:

>> On May 29, 2017, at 11:25 AM, Beverly Voth <[hidden email]> wrote:
>>
>> A table occurrence (table alias name ON the graph) must be used in ExecuteSQL queries.
>
> Beverly,
>
>    What is a "table alias name"?  I see a Source Table and Data Source on the graph but not a table alias.  Also is "ON" an acronym like "TO", or did you just capitalize it for emphasis?
>
> Thanks,
> Debbie
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

debt
In reply to this post by John Weinshel
> On May 29, 2017, at 11:14 AM, John Weinshel <[hidden email]> wrote:
>
> Try it without the WHERE clause first, to get started:
>
>
> ExecuteSQL
> (
> "
> SELECT Qty, Item_ID, Description
> FROM Q_LINE_ITEMS
>
> FETCH FIRST 2 ROWS ONLY
> "
> ;
> "--"; ""
> )

        I still get the question mark, so I'm assuming that my problem is the table name in the FROM clause (the local table works fine, the related table doesn't).  What exactly am I supposed to use there?  There's a Source Table and a Data Source, and then there's the TO name (is there one I'm missing?).  No matter which one I use, nothing changes.  Here are just a few of my attempts to get something to work:


ExecuteSQL ( "SELECT *
              FROM  Q_LINE_ITEMS|quote_ID
              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )

ExecuteSQL ( "SELECT *
              FROM  "Line Items\Q"
              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )

ExecuteSQL ( "SELECT *
              FROM  LineItems_Q
              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )

ExecuteSQL ( "SELECT *
              FROM  Q_LINE_ITEMS
              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )

---------

        O.K.  After reading Beverly's last email, I take it that the first example above should work, as it uses the TO to the related table but, sadly, it doesn't.  I must be really dense.  This is such a simple example. :(

Debbie
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Custom function to make list of multiple related fields

John Weinshel
Yes, the first example should work, which made me think Filemaker SQL
doesn't like pipes in table names; a simple test proved that point.

Is 'Q_Line_Items' the base table and 'Q_Line_Items|quote_ID' a TO based on
it? If so, you can use just the base table name; if not-- if
'Q_Line_Items|quote_ID' is the name of the base table, you'll need to
either rename the table (and any similarly named tables), or bail on SQL.

It's generally safest to stick to alphanumeric characters, possibly
underscores (but not leading underscores), and no spaces, if you want
Filemaker to work easily with other technologies.



On 5/29/17, 12:23 PM, "debt" <[hidden email]> wrote:

>> On May 29, 2017, at 11:14 AM, John Weinshel <[hidden email]> wrote:
>>
>> Try it without the WHERE clause first, to get started:
>>
>>
>> ExecuteSQL
>> (
>> "
>> SELECT Qty, Item_ID, Description
>> FROM Q_LINE_ITEMS
>>
>> FETCH FIRST 2 ROWS ONLY
>> "
>> ;
>> "--"; ""
>> )
>
> I still get the question mark, so I'm assuming that my problem is the
>table name in the FROM clause (the local table works fine, the related
>table doesn't).  What exactly am I supposed to use there?  There's a
>Source Table and a Data Source, and then there's the TO name (is there
>one I'm missing?).  No matter which one I use, nothing changes.  Here are
>just a few of my attempts to get something to work:
>
>
>ExecuteSQL ( "SELECT *
>              FROM  Q_LINE_ITEMS|quote_ID
>              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )
>
>ExecuteSQL ( "SELECT *
>              FROM  "Line Items\Q"
>              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )
>
>ExecuteSQL ( "SELECT *
>              FROM  LineItems_Q
>              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )
>
>ExecuteSQL ( "SELECT *
>              FROM  Q_LINE_ITEMS
>              FETCH FIRST 2 ROWS ONLY" ; "" ; "" )
>
>---------
>
> O.K.  After reading Beverly's last email, I take it that the first
>example above should work, as it uses the TO to the related table but,
>sadly, it doesn't.  I must be really dense.  This is such a simple
>example. :(
>
>Debbie
>_______________________________________________
>FMPexperts mailing list
>[hidden email]
>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
12