Hi,

I'm finally migrating from FM 6 to 8 an ran into a little problem with 
auto-entered values. Perhaps someone has had the same problem an can 
help me out:

I have a table "invoices" with a key (serial number) and a second table 
"items" which is display in a portal. The item records are created via 
this portal from the invoice layout. What I try to achive is to 
automatically number the items with a position nr 1..n for each invoice 
(without a script, which would work but would be clumsy).

Details:

I did it in FM 5 with a calculation field in "invoices" 
(max(items::posnr)+1) that I used to auto-enter the value for posnr (via 
a backwards relation from items to invoices). Unfortunately this does 
not always work in FM 8 any more. It does work when I "save" between the 
creation of item records, i.e. click into empty space in the layout. It 
does not work however, when I enter one item via the portal an tab right 
into the next line to enter the next item. All new items are numbered "2".

I think this may be a problem with the order in which the fields are 
filled. If the posnr field is filled first, the relation does not yet 
work and the maximum cannot be calulated. So I tried alternatively to 
set up a self relation from items to items based on the invoice key (so 
on item relates to all items of the same invoice) and auto-enter a 
look-up value based on the self relation. But this doesn't work as well...

Any ideas?

Thanks a lot in advance,
Marek

In article <43f5a73e$0$497$9b4e6d93@newsread4.arcor-online.net>, Marek
Habermann <marek.habermann@gmx.de> wrote:

> Hi,
> 
> I'm finally migrating from FM 6 to 8 an ran into a little problem with 
> auto-entered values. Perhaps someone has had the same problem an can 
> help me out:
> 
> I have a table "invoices" with a key (serial number) and a second table 
> "items" which is display in a portal. The item records are created via 
> this portal from the invoice layout. What I try to achive is to 
> automatically number the items with a position nr 1..n for each invoice 
> (without a script, which would work but would be clumsy).
> 
> Details:
> 
> I did it in FM 5 with a calculation field in "invoices" 
> (max(items::posnr)+1) that I used to auto-enter the value for posnr (via 
> a backwards relation from items to invoices). Unfortunately this does 
> not always work in FM 8 any more. It does work when I "save" between the 
> creation of item records, i.e. click into empty space in the layout. It 
> does not work however, when I enter one item via the portal an tab right 
> into the next line to enter the next item. All new items are numbered "2".
> 
> I think this may be a problem with the order in which the fields are 
> filled. If the posnr field is filled first, the relation does not yet 
> work and the maximum cannot be calulated. So I tried alternatively to 
> set up a self relation from items to items based on the invoice key (so 
> on item relates to all items of the same invoice) and auto-enter a 
> look-up value based on the self relation. But this doesn't work as well...
> 
> Any ideas?
> 
> Thanks a lot in advance,
> Marek

Auto-enter fields can be a little flakey.

I've often found it best to add an extra If statement into the
calculation.
eg.
     Instead of
              (Max(Items::PosNr)+1)

     Use
              If(IsEmpty(KeyLinkField), "", Max(Items::PosNr)+1))

where KeyLinkField is the field used to define the Items relationship.

By forcing the calculation to return 'nothing' (ie. "") when the
KeyLinkField is empty (ie. not yet go a value defined) the auto-enter
field isn't counted as having been auto-entered and is therefore left
and properly calculated when the KeyLinkField is no longer empty.


Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Marek Habermann wrote:

> I have a table "invoices" with a key (serial number) and a second table 
> "items" which is display in a portal. The item records are created via 
> this portal from the invoice layout. What I try to achive is to 
> automatically number the items with a position nr 1..n for each invoice 
> (without a script, which would work but would be clumsy).
> 
> I did it in FM 5 with a calculation field in "invoices" 
> (max(items::posnr)+1) that I used to auto-enter the value for posnr (via 
> a backwards relation from items to invoices). Unfortunately this does 
> not always work in FM 8 any more. 

Portal records don't always update as you'd expect them to.  The joined 
records are stored in a cache that doesn't get flushed until the record 
is committed.

More importantly, a change in 8 from previous versions is that portal 
rows will evaluate now from the perspective of the portal row while 
you're cursor is in a portal row.  This means that while your cursor is 
in one of the portal rows, (max(items::posnr)+1) on a new row will 
always equal 1.  You are evaluating the posnr field in Items, which from 
the context of an items portal row is just that one row.

One way you may be able to get around these limitations and changes is 
to create a relationship from Items to a second table occurrence of 
items, from InvoiceID to InvoiceID.  Make the auto-enter calc for posnr 
based on that relationship: (count(items_for_invoice::posnr)+1).  No 
guarantees this will work, as I have not directly tested it...but in 
theory...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Professional Solutions, Inc.    Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

Helpful Harry wrote:

>      Instead of
>               (Max(Items::PosNr)+1)
> 
>      Use
>               If(IsEmpty(KeyLinkField), "", Max(Items::PosNr)+1))

hmm, I tried different variations, but it didn't make a difference im my 
case :(

Howard Schlossberg wrote:

> One way you may be able to get around these limitations and changes is 
> to create a relationship from Items to a second table occurrence of 
> items, from InvoiceID to InvoiceID. Make the auto-enter calc for posnr
> based on that relationship: (count(items_for_invoice::posnr)+1).

Hmm, yes, I tried that. That's what I tried to explain with my "self
relation from items to items", it didn't work either. I tried both
"count" and "max". :(

In article <440478a1$0$22075$9b4e6d93@newsread2.arcor-online.net>,
Marek Habermann <marek.habermann@gmx.de> wrote:

> Helpful Harry wrote:
> 
> >      Instead of
> >               (Max(Items::PosNr)+1)
> > 
> >      Use
> >               If(IsEmpty(KeyLinkField), "", Max(Items::PosNr)+1))
> 
> hmm, I tried different variations, but it didn't make a difference im my 
> case :(

The only other thing I can think of is to try turning off (or on if
it's off) the "Do not evalute if all referenced fields are empty"
option at the bottom of the Auto-enter Calculation window - it's a
little buggy at times.

I just had a database that used a similar auto-enter function of
Max(Relationship::Date). It worked fine with the option turned on
(although the data didn't show until the user finishes entering the
related record), but then I had to change the calculation to be just
Status(CurrentDate) and it failed to work at all until I turned that
option off.   :o\

Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)