Calculated Field Type using previous value based on Order #

[jpayne]

This should be common but I can't figure out how to do this correctly:
I have an Complete Order Set - Key Order Number (is linking key between both tables)
With an Order table that has multiple Order Numbers to multiple order lines
With an Order Lines table (one to many)

I want the line id to increment based in relation to the qty of lines in the order.

For example:
Order No: 25569
Will have 5 lines to it
I want the first line to have the line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005

If I then go onto Order No: 359846
The 1st line id should be 001
2nd line id 002, etc.

and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 006 and then if there is another line no 007, etc.


Where and how should I go about getting this to happen.
I was doing it in the table rules for line id: but I don't know if I should do Field Type as Calculated and then what calculation should I use? I tried last() but it says I can't use that function.
I've tried in Data Entry and default mode using this : PREVIOUS("line","OrderNo")+001

Bbut when I go to the next order it continues the Line id sequence it does not rely on the how many lines are per order.
It ends up looking like this:

Order No: 25569
1st line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005

If I then go onto Order No: 359846
1st line id: 006
2nd line id: 007

and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 008 and then if there is another line no 009, etc.


I want it to look like this:
Order No: 25569
1st line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
6th line id: 006
7th line id: 007

If I then go onto Order No: 359846
1st line id: 001
2nd line id: 002


Any help would be appreciated.

Jennifer Payne

[armasoft]

I'm not sure if you can do this in field rules, but we code this as follows:

'Get current line count for this invoice
dim my_line_no as N
query_filter = "invoice_no = " + my_invoice_no
line_cnt = a5_get_records_in_query("invoice_items",query_filter)
my_line_no = line_cnt+1

You'd need to manipulate the result to add leading 0s if required.

[Tom Cone Jr]

Storing line numbers in the one to many child table is problematic. Recommend thinking about their purpose. What's the goal? Are you numbering them because they must be in a particular order, or simply to display the order in which they were entered? What if the user wants to delete a row after entering later rows? If you don't need them in the table, don't use them. If you don't have them in the table I think there's a way to number lines in the details band of your later report, anyway.

On the other hand if you're entering lines using a browse, and if the sequence of the lines is important, you can store a user entered line number in each row. Forget automatic numbering. Display the "order" of the rows in your browse using the line number field. This lets the user move rows around by renumbering them to suit.

[jpayne]

Storing line numbers in the one to many child table is problematic. Recommend thinking about their purpose. What's the goal? Are you numbering them because they must be in a particular order, or simply to display the order in which they were entered? What if the user wants to delete a row after entering later rows? If you don't need them in the table, don't use them. If you don't have them in the table I think there's a way to number lines in the details band of your later report, anyway.

On the other hand if you're entering lines using a browse, and if the sequence of the lines is important, you can store a user entered line number in each row. Forget automatic numbering. Display the "order" of the rows in your browse using the line number field. This lets the user move rows around by renumbering them to suit.

It would be for our installers so when they said they did task number... we can see what task that is.
Or think of Purchase orders with lines.
001 Install bathroom sink
002 Move Fan in bathroom
003 Tile the Bathroom Shower

[Mike Wilson]

I believe you are trying to establish a work sequence order for delivery to installers. However you are trying to establish a work sequences order by entry order. I have a business that has a dynamic installation process and there is no possible way that record entry order is the outcome of work sequence order. When the bathroom counter is decided to be replaced after you have entered 001 Install bathroom sink, what exactly happens in your system, because the counter has to be set before the sink can be set. No chance what you are proposing would work for me. That is why I have a field specifically to order the work events and upon every entry of a new work event, all the work events are sequenced. Simple stuff using xdialogs and direct table entries. I can rustle up an example if you are seriously interested.

[Al Buchholz]

If this is for standard installations, why not have a standards table?

Then you enter an order, pick a standard, and generate all of the lines for that order.

If that makes sense then we can talk about how to do it.

ie standards table, pick a standard, append operation to add to order lines by standards key

[Ted Giles]

Maybe, Al.
However, the client may decide to have a different order of work.
What I don't get is the need for a defined order numerically.
A job is a job with consumables and labour, so if "tile shower" is a job, then I am presuming there would be N sq mtrs tiles, N tubs grout, n hours labour + the unforseen factor.
Would the client or installers care about the job number?

Client Ref/Invoice# + Job Activity would make more sense to me.

Jpayne, got more info?

Btw. I have an on line estimating tool showing profit and loss on jobs if you are interested.

[Al Buchholz]

Maybe, Al.
However, the client may decide to have a different order of work.
What I don't get is the need for a defined order numerically.
A job is a job with consumables and labour, so if "tile shower" is a job, then I am presuming there would be N sq mtrs tiles, N tubs grout, n hours labour + the unforseen factor.
Would the client or installers care about the job number?

Client Ref/Invoice# + Job Activity would make more sense to me.

Jpayne, got more info?

Btw. I have an on line estimating tool showing profit and loss on jobs if you are interested.

Yes. By appending the records into the line table, then line values can be changed for that job.

[jpayne]

The Client Ref/Invoice# + Job Activity may be closer to what Im going for. I'm unfortunately not explaining it clearly enough.
It's a work order of sorts. And they installer may reference a task number if say: Task No. 1. Install bathroom Sink, they did this task but ran into a problem or addition to this work - for instance they need to let our Project Manager know that when they installed they completed task 1. They chipped the tile and now they need to order the same tile as in the task 3. (Tile from bathroom shower is same tile used as back splash on the sink).

There may need to be a reference and referencing task numbers:
1. Task complete w/damage, order more tile - same tile as task 3.
Vs.
1. Task complete w/damage, order same time that is used in bathroom shower.

And as you can tell one day at house 1 (purchase order 25569) can have 3 things and then on another day return for the fixing of the tile on Task 1 - that would be a NEW TASK and require a new task number for that PO which would then be Task/Line 4.

But what I run into is currently because I've entered tasks for 3 other purchase orders with varying amount of tasks to be done on each. Instead of PO 25569 have a 4th task to fix the tile. I get a task number of 28. The installers and PM will look at that and think where are the other 25 other tasks. (So maybe this is more for the PM to track tasks requested and tasks completed per work order/PO)

Here is a screenshot:
FYi, there are 2 databases here the (form is one), the browse is the second - linked by the 20-A307 or JOB NUMBER)
And the 2 Vanity tops is a basic description. Most of those basic descriptions would be Bathroom 256 and Bathroom 300
And then the tasks would be the specifics - one of which would be from this basic description -
1. Install 2 vanity tops
(Data entry got lazy and just put the task in)JobEntry.JPG

[Ted Giles]

Snagging.zip

In the UK we have a name for fixing stuff after the installation.
I'ts called "Snagging".

Have a look at the attached for ideas.

[Mike Wilson]

Ted,
Thanks for the culture lesson and the example. I think you are spot on. The broken tile from the sink install is a child of the sink install. And the sink install can't be completed when there is a need to order tile from task 3 and fix the snag

[Ted Giles]

Ted,
Thanks for the culture lesson and the example. I think you are spot on. The broken tile from the sink install is a child of the sink install. And the sink install can't be completed when there is a need to order tile from task 3 and fix the snag

Mike, let me know if you get this as a Notification Email please.
I'm trying to work out why I still dont get notifications!

[Mike Wilson]

Hi Ted,
I received this as a notification

[Ted Giles]

Hi Ted,
I received this as a notification

When you quote someone, then a Notification is sent. If you don't, then there doesn't seem to be one.
I'll ask Lenny - if he is still talking to me that is!