help with Calculation

[Dani R]

i what to make a serial number but not for all the lines only if i have a conditional term . how do i do it?

[bcooney]

Can you give us the details of your scenario? A serial number is usually created for each record and used as a key field, and so it's a bit odd that you would only want it created conditionally.

[Søren Dyhr]

i what to make a serial number but not for all the lines only if i have a conditional term . how do i do it?

Establish a found set, then use the serialisation feature of scripted Replace ... then finally show all records again. That is if you demand only should be regarded literally - but Barbara have a point, such a desire usually .... carries a wrong metaphor as stowaway!

--sd

[Dani R]

I have 1 filed that is serial number what is a temp order id .

Only when i run s script that will ok the order . i what is a deferent filled to put a new serial number what is the published one . this means that not all the temp order lines will have a order id.

thank you Dani R

[bcooney]

Dani,

Let me confirm my understanding of your question. You are creating orders, but they are considered "temporary" until they are "published." All orders, not matter their status, have an auto-entered serial number, "temp order ID."

Once an order is published, you want to create a new OrderID, but it does not equal the temp order ID.

Is this correct?

Do you care if OrderIDs are non-sequential? (Hopefully the answer is no).

[Dani R]

No I do not care .

Thank you DaniR

[Søren Dyhr]

Do you care if OrderIDs are non-sequential? (Hopefully the answer is no).

I wonder why this a concern of yours? Both ID's could be sequential ... just make a cartesian selfjoin relation, and when ever a "Published" state is reached is for a few records make a found set of them and issue this single line script:

Replace Field Contents [ Untitled::PublishedSerial; Replace with calculation: Case ( IsEmpty ( Untitled::PublishedSerial ); Max ( myCartesian::PublishedSerial )+1;Untitled::PublishedSerial) ] 

But why not use a Set Field[ in a button on the record which does the same?

--sd

[bcooney]

Well, Soren, I was thinking, why not just have an OrderID that doesn't show unless the order is published? So, each order gets an ID, you control its display.

You method is fine, too. I'm a bit concerned about the need for a found set to determine the next serial.

This brings me to keys. What are you using for the Order key field and the foreign key in OrderLineItems?

[Dani R]

this is what , i have in the table a few fields that field 1 is temp_orderID what always has a number there . is Final_orderID only when i publish by a button running a script in the script i what for the script to go to Final_orderID and find the biggest number and add +1 to it.

thank u DaniR

[Søren Dyhr]

This brings me to keys. What are you using for the Order key field and the foreign key in OrderLineItems? /

Yes that's a good point, something could then then being considered in "Option" until the published field gets a value, if the "stock" then should be established are we bound to make a subsummary report ... not something really to hate??

--sd

[bcooney]

Dani, you rate yourself as a beginner. This technique of manually creating IDs is dangerous. Do you understand how key fields work? What keys are you using.

[Dani R]

what key?

i what to cliffy that in FM i am beginner but before i was whiting sites in php,mysql.

[comment]

AFAIK, there are only two RELIABLE methods to accomplish this. One is to turn the existing serial number into OrderID (thus having gaps in between OrderID's), the other one is to add a table for numerating the orders, as described here:

http://fmforums.com/forum/showpost.php?post/172165/

http://fmforums.com/forum/showpost.php?post/226912/

BTW, I am tired of repeating this once again, like some old Cato:

http://fmforums.com/forum/showpost.php?post/274682/

[Søren Dyhr]

AFAIK, there are only two RELIABLE methods to accomplish this. One is to turn the existing serial number into OrderID (thus having gaps in between OrderID's), the other one is to add a table for numerating the orders, as described her

I know! ....but solicited solutions have gone out of fasion :

I wondered what Barbera meant but, yes it's correct ... if the solution should be multi user. Or are there other way the ID'ing could go corrupt, in single user mode?

Ok there is another problem, if records gets deleted, but it should be stopped via the privileges set, because lack of privileges would similar allow access to a counter tables as Comment suggests as reliable method.

But having spend just a short while as part of the staff in an accounting firm, have shown me if someone deliberately will cook the book ... will all preventions in the long run be circumvented.

--sd

[bcooney]

That's what I thought...Dani, each record needs a unique ID within the same table. Your Orders need a unique ID, and the best way to do that is to create a number field and use the entry options to set it to be a serial number and do not allow users to change it.

This OrderID will be stored in the OrderLineItem table as a foreign key, and the tables related by matching the primary OrderID to the foreign OrderID in the OrderLineItems table.

So, my suggestion to you is to just have one field OrderID and a status field. The status field can be changed by a user from "Temporary" to "Published" or whatever terms you want to use.

Are you also calc'ing Inventory levels?

[T-Square]

Comment--

Good call. I myself wonder why, if the ID sequence is unimportant, the OP couldn't just have a Boolean field OrderIsReal to transform the order from "temporary" to "real"? Then, change temp_order_id to Order_ID and use the Boolean for management purposes.

David

[comment]

I would prefer to record the actual date of finalizing the order, instead of a boolean. Either way, OrderID could be a simple calculation =

Case ( Final ; SerialID )

if OrderID is necessary at all, in the circumstances.

[Søren Dyhr]

if OrderID is necessary at all, in the circumstances.

You mean why bother when it's stored in the database anyway, and it's way makes order in the mess get's irrelevan - it's only when the dealings get's on paper it get's important?

actual date of finalizing the order

Since it's required anyway, and stuffed inside the Case( statement, does it make sense as soon as a value it entered?

Both are excellent point's indeed!

This means you're not particular fond of the counter table anyway, because a crook would learn how to get the privilege sets cracked, and change the foundation for the counting by deleting a record, and issue a:

Set Next Serial Value[

However is it still touched evasively ... is deletion of records really an issue? Strictly speaking should a regretted order/invoice be countered by note of credit, not deleted at all!

--sd

[comment]

This means you're not particular fond of the counter table anyway

It's not a matter of my personal preference: OP was asked "Do you care if OrderIDs are non-sequential?" and the reply was "No I do not care." In these circumstances, I presume deleting an order may not an issue either (though it could be prevented in privileges).

You and I may shake our heads at such haphazard practices, but if business rules and/or tax authorities do not insist on stricter controls, there's no reason why it wouldn't work.

a crook would learn how to get the privilege sets cracked

I don't plan for such contingencies - if someone cracks the file, they can do whatever they want, and it doesn't matter at all what I did.