ESS and globals

[Robertson, Forbes-2]

Hi All

I have been using ESS for some reports and I would like to have a
selection based on product code and a date range.

Normally I would do this by creating a couple of global fields
(startdate & enddate) in the parent table and setting up a relationship
using these fields and the product code.

But with an ESS table you can't create global fields

I have tried various options but went back to selecting the fields in a
SQL statement and doing a simple ODBC import.

Thanks
Forbes


--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged.  It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient,, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

postmaster@...

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

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

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[G. Pupita]

Hi Forbes

>
> I have been using ESS for some reports and I would like to have a
> selection based on product code and a date range.
>
> Normally I would do this by creating a couple of global fields
> (startdate & enddate) in the parent table and setting up a  
> relationship
> using these fields and the product code.
>
> But with an ESS table you can't create global fields

I may be missing something but couldn't you create the globals in a  
(any) FileMaker table, relate them to the ESS table as you mentioned  
and select pertinent records using the relationship and a GTRR step ?


Ciao
G. Pupita
Certified FileMaker 9 Developer
FileMaker Business Alliance



_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Robertson, Forbes-2]

This was my first thought but how would it work?

I have table A <-> B on product code
Then if I have table X with the globals, how do I set it up so that the
globals are involved in the relationship? What I can't see is how I
would use fields in two tables both on the same side of the
relationship?

The added wrinkle to this, is that this is a stock report, so the parent
record are stock items and the child records are transactions. But I
need the report to show stock items with no transactions. So I think
that it has to run from the stock table, rather than the transaction
table.

Forbes

-----Original Message-----
From: fmpexperts-bounces@...
[mailto:fmpexperts-bounces@...] On Behalf Of G. Pupita
Sent: 18 August 2008 11:02
To: fmpexperts@...
Subject: Re: ESS and globals

Hi Forbes

>
> I have been using ESS for some reports and I would like to have a
> selection based on product code and a date range.
>
> Normally I would do this by creating a couple of global fields
> (startdate & enddate) in the parent table and setting up a  
> relationship
> using these fields and the product code.
>
> But with an ESS table you can't create global fields

I may be missing something but couldn't you create the globals in a  
(any) FileMaker table, relate them to the ESS table as you mentioned  
and select pertinent records using the relationship and a GTRR step ?


Ciao
G. Pupita
Certified FileMaker 9 Developer
FileMaker Business Alliance



_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged.  It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient,, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

postmaster@...

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

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

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[G. Pupita]

Hi Forbes

> This was my first thought but how would it work?
>
> I have table A <-> B on product code
> Then if I have table X with the globals, how do I set it up so that  
> the
> globals are involved in the relationship? What I can't see is how I
> would use fields in two tables both on the same side of the
> relationship?
>
> The added wrinkle to this, is that this is a stock report, so the  
> parent
> record are stock items and the child records are transactions. But I
> need the report to show stock items with no transactions. So I think
> that it has to run from the stock table, rather than the transaction
> table.

I've problems to understand your situation
In your first mail you mentioned an approach using globals to filter  
related records, now it seems to me that you're working on a found  
set ...
I'm surely missing something ;-)

Ciao
G. Pupita
Certified FileMaker 9 Developer
FileMaker Business Alliance



_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Robertson, Forbes-2]

Its probably my explanation.

In the parent table Products, find a sub-set of records, then for these
records I want to show a report of stock transactions within a date
range. But for products with no movements I still need a line in the
report.

Therefore I have been running the report from the Products table, rather
than the child Transactions table. For normal FM tables I would have a
relationship between the two tables

Products     Transactions
Product Code    =    Product Code
zgStartDate    <     Date
zgEndDate      >     Date

Which gives me total transactions within the period, for the report.

But using ESS I can't use globals in the Products table to create the
relationship.

Is this clearer?

Forbes

-----Original Message-----
From: fmpexperts-bounces@...
[mailto:fmpexperts-bounces@...] On Behalf Of G. Pupita
Sent: 18 August 2008 12:16
To: fmpexperts@...
Subject: Re: ESS and globals

Hi Forbes

> This was my first thought but how would it work?
>
> I have table A <-> B on product code
> Then if I have table X with the globals, how do I set it up so that  
> the
> globals are involved in the relationship? What I can't see is how I
> would use fields in two tables both on the same side of the
> relationship?
>
> The added wrinkle to this, is that this is a stock report, so the  
> parent
> record are stock items and the child records are transactions. But I
> need the report to show stock items with no transactions. So I think
> that it has to run from the stock table, rather than the transaction
> table.

I've problems to understand your situation
In your first mail you mentioned an approach using globals to filter  
related records, now it seems to me that you're working on a found  
set ...
I'm surely missing something ;-)

Ciao
G. Pupita
Certified FileMaker 9 Developer
FileMaker Business Alliance



_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged.  It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient,, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

postmaster@...

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

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

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[G. Pupita]

Hi Forbes

> Its probably my explanation.
>
> In the parent table Products, find a sub-set of records, then for  
> these
> records I want to show a report of stock transactions within a date
> range. But for products with no movements I still need a line in the
> report.
>
> Therefore I have been running the report from the Products table,  
> rather
> than the child Transactions table. For normal FM tables I would have a
> relationship between the two tables
>
> Products     Transactions
> Product Code    =    Product Code
> zgStartDate    <     Date
> zgEndDate      >     Date
>
> Which gives me total transactions within the period, for the report.
>
> But using ESS I can't use globals in the Products table to create the
> relationship.
>
> Is this clearer?

Yes :-)
You might use a script to retrieve the IDs of selected products, put  
them into a text global in another table in which you also have  
globals for start/end date, and then create the appropriate  
relationships towards the Transactions table
You'd end up with something like
Other table     Transactions
global with ids =    Product Code
zgStartDate    <     Date
zgEndDate      >     Date

Hope this helps,

Ciao
G. Pupita
Certified FileMaker 9 Developer
FileMaker Business Alliance



_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Jim McNeely]

I've been doing this by creating a shadow table in FM that has only  
the primary key from the ESS table and then whatever associated  
global or other temporary type fields the relationship requires. You  
could also create a calc that references a single record table with a  
global field in the ESS table, but I don't think the refresh always  
comes through as you want when you change the global value.

HTH

Jim McNeely

On Aug 18, 2008, at 2:11 AM, Robertson, Forbes wrote:

> Hi All
>
> I have been using ESS for some reports and I would like to have a
> selection based on product code and a date range.
>
> Normally I would do this by creating a couple of global fields
> (startdate & enddate) in the parent table and setting up a  
> relationship
> using these fields and the product code.
>
> But with an ESS table you can't create global fields
>
> I have tried various options but went back to selecting the fields  
> in a
> SQL statement and doing a simple ODBC import.
>
> Thanks
> Forbes
>
>
> --
> **********************************************************************
> **********
>
> The information in this email and any files transmitted with it is  
> confidential
> and may be legally privileged.  It is intended solely for the  
> addressee and
> others authorised to receive it.
>
> If you are not the intended recipient,, any disclosure, copying,  
> distribution or
> action taken in reliance on its contents is prohibited and may be  
> unlawful.
>
> Prospects Services Limited is a limited company registered in  
> England and Wales.
>
> Reg No: 3042176
> Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1  
> 1LT.
>
> If you have received this email in error please notify:
>
> postmaster@...
>
> http://www.prospects.co.uk
>
> This footnote also confirms that this email message has been swept  
> for the
> presence of computer viruses
>
> **********************************************************************
> **********
>
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Robertson, Forbes-2]

Thanks Jim and G.

Different work arounds to the one I choose (do a SQL select then ODBC
import) -- but all in all it is another limitation of ESS. Filtered
relationships are pretty common and having to build big work arounds
doesn't really add to ESS being ready for prime time.

Hopefully the ESS functionality will continue to improve in future
versions of FM as the concept is good, if a bit limited.

Cheers
Forbes


-----Original Message-----
From: fmpexperts-bounces@...
[mailto:fmpexperts-bounces@...] On Behalf Of Jim
McNeely
Sent: 18 August 2008 15:57
To: fmpexperts@...
Subject: Re: ESS and globals

I've been doing this by creating a shadow table in FM that has only  
the primary key from the ESS table and then whatever associated  
global or other temporary type fields the relationship requires. You  
could also create a calc that references a single record table with a  
global field in the ESS table, but I don't think the refresh always  
comes through as you want when you change the global value.

HTH

Jim McNeely

On Aug 18, 2008, at 2:11 AM, Robertson, Forbes wrote:

> Hi All
>
> I have been using ESS for some reports and I would like to have a
> selection based on product code and a date range.
>
> Normally I would do this by creating a couple of global fields
> (startdate & enddate) in the parent table and setting up a  
> relationship
> using these fields and the product code.
>
> But with an ESS table you can't create global fields
>
> I have tried various options but went back to selecting the fields  
> in a
> SQL statement and doing a simple ODBC import.
>
> Thanks
> Forbes
>
>
> --
> **********************************************************************

> **********
>
> The information in this email and any files transmitted with it is  
> confidential
> and may be legally privileged.  It is intended solely for the  
> addressee and
> others authorised to receive it.
>
> If you are not the intended recipient,, any disclosure, copying,  
> distribution or
> action taken in reliance on its contents is prohibited and may be  
> unlawful.
>
> Prospects Services Limited is a limited company registered in  
> England and Wales.
>
> Reg No: 3042176
> Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1  
> 1LT.
>
> If you have received this email in error please notify:
>
> postmaster@...
>
> http://www.prospects.co.uk
>
> This footnote also confirms that this email message has been swept  
> for the
> presence of computer viruses
>
> **********************************************************************

> **********
>
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au