Automatic Genration of Line Numbers in fp8

[steelhorse]

I have recently converted a database from Fp6 to fp8 where it worked very nicely- I have an Order table and an Order Lines table with a relationship connecting them by order number - I generate a unique sequential line number for each order line by auto entering a calculated value based on the Orders::Next Line field - Count(Order Lines::Order No) + 1 - an Order Lines Portal in the Order Layout allowed creation of order lines with the line numbers incrementing (i.e 1, 2 etc) in 8 now this only works if I click away from the portal after I created each Order Line otherwise the line number remains the same - it looks like the Orders::Next Line field is cached. I don't want to do anything with scripts as it would mean the user having to click a button after having entered ach line - equally I'm loathe ti use the Set Next Serial script step as more than one user could be creating an order at any one time - it feels like there should be an automatically save changes option on a portal - any help greatfully received

[bcooney]

Why not just put @@ on the portal row? It'll show a line number.

[steelhorse]

Thats true however I need the line number to be part of a key in order lines - I have to reconcile deliveries against each order line e.g Order 991 has 2 lines 991-1 and 991-2 - 991-1 has 2 deliveries allocated to it and 992-2 has 3

Thanks for your prompt response

[bcooney]

Now I'm lost. Each OrderLineItem is a record, correct? If so, then it has a unique serial id key. Each OrderLI is then a parent to a delivery line item, right? So, why does the portal line number have anything to do with this?

Also, you don't need a script if you have the relationship set to allow the creation of related records.

[steelhorse]

I want Order number 995

this has 3 order lines which need to have a line number in them so eg line 1, line 2 line 3

so the Order 995 has the 3 order lines 995-1 995-2 995-3

When I put in a new order 996 then the order lines for that will be numbered 996-1 996-2 etc

these are created in a portal to the order viae the Order Lines relationship - in filemaker 6 each time you created a new line in the portal the line number incremented by 1

when I match a delivery against an order line I match it to the appropriate number e.g 996-2 995-1 etc

This all works fine in Fp6 and has done for years

However:

when I create the order line in fp8 - the Order line number doesn't increment by 1 unless you click out of the order line portal after you create each line (i.e committing the record presumably)

the incrementing order line number is achieved by creating a recursive relationship to order lines on order number and doing a Count (e.g Next Order line Calculation Count(Number of Order Lines::Order No)

whilst I could have a sequential order line number from an from an Auto Enter Serial field it would be difficult to reconcile the delivery against it as it would have no reference to the order number in it

I agree you don't need a script to create related records - the only way I could see of getting round it was having an from an Auto Enter Serial field for the line number and making a script that

set it to the number of order lines records currently in the order - which is something I want to avoid as you can imagine

[Søren Dyhr]

Well I came to think of this:

http://www.briandunning.com/cf/62

...which I would use this way, see attached template!

--sd

invoicing.zip

[comment]

This all works fine in Fp6 and has done for years

Well, there was no record committing in version 6. It's true, there is a problem of looking up data from an uncommitted parent, so you'll just have to find some other method.

The simplest way would be to use auto-entered serial numbers for the line items.

it would be difficult to reconcile the delivery against it as it would have no reference to the order number in it

You could very easily add the parent order number to display on the layout - or define a calculation field as a concatenation of order number & line number.

Alternatively, you could calculate the line's sequential number as Søren suggested - with a side-effect of the numbers recalculating to close the gap after a line is deleted.

[comment]

I think it could be a lot simpler.

invoicing.fp7.zip

[Søren Dyhr]

Whether it's simpler or not, is a good question - but it removes an artifact my approach has, namely the short moment in the newly created first portal rows lifetime where Id-0 occures.

But the calc' you use, ought to be a tiny bit faster, due to the number of functions involved, but we have no idea as which is faster than the other, it's not like assembler where you know exactly how many clockings each command takes to execute - so one combo of functions might be faster than another one perhaps simpler to write ... we can only guess!

When we're at it why do you use a Case( here??

--sd

[comment]

It's true we never defined "simple" - still, I believe removing a recursive CF while keeping the same number of fields and TO's should count as simplifying. Speed, I think, is another issue. I agree that what's simpler for the developer may not always be simpler for Filemaker, and vice versa.

why do you use a Case( here??

Because originally the calc was just:

Count ( PrevLines::LineID ) + 1

with the thought that the OrderID could be added on the layout as a merged field. This needs to evaluated always, or it won't show for the first line - and then the Case() stops evaluation for the ghost child.

[Søren Dyhr]

You're right, but there's just one thing where do you find the recursion in the CF I used here ... it escapes me???

--sd

[comment]

Sorry, I didn't look at it closely. Since it's not recursive, why not just put it in the formula itself? I could understand using a non-recursive custom function if it's needed in several places, but this way only increases the computational overhead, I think.

[Søren Dyhr]

It's true we never defined "simple" - still, I believe removing a recursive CF while keeping the same number of fields and TO's should count as simplifying. Speed, I think, is another issue. I agree that what's simpler for the developer may not always be simpler for Filemaker, and vice versa.

I've just my attempt to catch up with some prosponed reading stumbled over this, giving answers to some of the things we'd wondered about in this thread:

http://fmcollective.com/2007/08/09/devcon-2007-closing-session/#more-72

Does FMP 9 evaluate calculation expression in a specific order?

A. We use a postfix form….a+b is really “a, b +”…we have to start ‘inside out’…prior to v7, FileMaker will evaluate the entire expression. That has been improved, short circuited, evaluates only when it has to Does it matter how you construct the expression? It can matter if you are referencing same fields several times in the expression…everytime you do that reference, you have to go get that value…use let function, use variable now you can reference variables much quicker than going to the reference…use recursion…you can take advantage of tail-recursion if the main function is isolated.

--sd

[comment]

Thanks, that's a VERY interesting link. It's too bad FMI doesn't publish such information officially.

I found the answer regarding the cost of Evaluate() particularly interesting. You may recall several occasions where I have suspected that is so. Although the report doesn't say so explicitly, I believe the same must be true for a custom function - even a non-recursive one. IOW,

Sum ( FieldA ; FieldB )

should evaluate faster than:

CustomSum ( FieldA ; FieldB )

where CustomSum() is defined as Sum ( a ; b ).