Calculated field issue

[Beltronics]

I use a user entered field to produce a purchase order number. I am happy with the results but have a glitch. I use a default value based on a formula which is the vendor ID plus a " - " and then the julien date and I make it a unique field. This is working fine but my user is now having a problem with only being able to produce one purchase order for a particular vendor once a day. If I use a calculated field as described, can I add an increment letter like A for the first PO and B for the second in the formula?

Here is my formula.
ltrim(str(VENDOR_ID)+"-"+alltrim(str(jdate())))

[Stan Mathews]

Re: Calculated field issue

You'd have to check for previous orders for the same vendor on the same day to tell what letter to use.

Might be easier to add a time field to the purchase order table, populate it with "now()" as the order is created, and require that the purchase order and time field combination be unique.

Try this... (substitute your actual tablename for "purchase_orders" and your actual fieldname for "po".

LTRIM(STR(VENDOR_ID)+"-"+ALLTRIM(STR(JDATE())))+"-"+CHR(65+TABLECOUNT("[COLOR="Red"]purchase_orders[/COLOR]","word([COLOR="red"]po[/COLOR],1,\"-\",2) = "+QUOTE(LTRIM(STR(VENDOR_ID)+"-"+ALLTRIM(STR(JDATE()))))))

[G Gabriel]

Re: Calculated field issue

If I use a calculated field as described, can I add an increment letter like A for the first PO and B for the second in the formula?

Yes, but first, let's simplify your expression:

dim vendor_ID as n=123
?ltrim(str(VENDOR_ID)+"-"+alltrim(str(jdate())))
= "123-2454825"
'Simplified:
?vendor_ID+"_"+jdate()
= "123_2454825"

To add letters is possible but involves more steps than you think:
If a user enters an order, it will have "A" at the end. If he starts to add another, how is the system to know what was the last entery for that user? Was it nothing? or A? or B?
So before adding a new record, you have to search if there were any entries by that user for this day. You could do that using dbmax(). Are you getting my point that this is unncessarily complicated.

I can't see you insisting on doing this, but if you do, here is the expression:

?vendor_ID+"_"+jdate()+"A"
= "123_2454825A"

?increment_value(vendor_ID+"_"+jdate()+"A")
= "123_2454825B"

Perhaps a better way is to add the time of day instead of A, B...etc.

?vendor_ID+"_"+time("MMddyyyy0h0mss",now())
= "123_1224200807562626"

[Beltronics]

Re: Calculated field issue

Thanks guys but, I can't see using these suggestions. I know the A B etc thing is way too complicated but the time suggestion makes the PO number way too long.

Here's the problem that I am encountering and that you are not aware of. A simple auto increment number would be fine but my particular problem (self created) is that I create the PO number twice because field rules don't operate in "operations".

When the operator decides to order parts, each part they select to order gets posted to an "order" table and that operation creates a PO number to post with the part. Then, when the selection process is done, (there are many parts in the "order" table with different PO numbers) the operator selects to "Create Purchase Orders" by clicking an action button. This invokes a report with an PO Header with a field rule creating the PO number again (based on the vendor and the julien date). This then selects all the parts in the "order" table with that PO number and groups them into purchase orders. So, I need to look in the Order table and pick out the unordered parts (I mark them when they have been ordered) and create a PO Header using the same PO number created in the ORDER table. So far, this works fine except when the operator wants to order twice from the same vendor on the same day.

I have no special attachment to the way the PO number ends up looking. An auto increment from BELT90001 and on would be acceptable so any suggestions would be appreciated. If you guys had set up this system, I'm sure you would have chosen a different road but, I'm on the highway now and must keep driving forward.

[G Gabriel]

Re: Calculated field issue

You already have your answer as to how to add A,B,C... to your ID.
ID=increment_value(dbmax(....for key value use vendor_ID+"_"+jdate()...))

[Beltronics]

Re: Calculated field issue

Thanks everyone but this one's beyond me. I'll restructure the ordering system with what knowledge I have. I just have to dig a new hole since I can't fill this one.