Here I am again, begging for help

[Beltronics]

I have two tables in a set.
PO_header.dbf and Orders.dbf. PO_header is the parent, Orders is the child, one to many linked on Vendor Name.

When the operator wants to order items, they get "appended" to the Orders.dbf and just sit there. This works fine and am not looking to change this.

Now: The PO_header table has an auto increment field called PO Number.
It also has a vendor name field.

I want a utility that will open the PO_header table, generate a new PO number with the auto increment feature, then go into the Orders table, find all the items with a field called "po_created" set to .f. and take each unique vendor name and post it to the PO_header, creating a new PO_header record for each unique vendor name with a PO_created = .f. Then, the PO_number created in PO_header should be added to the PO_NR field in the orders table and the PO_created field set to .t.

So, create a new PO_header record, take the resulting PO_NR, go into ORDERS, find records with no PO_NR and PO_Created set to false, add the PO_number while taking the vendor name from ORDERS and posting it to PO_Header. Then, find the next set of records in ORDERS with a different vendor name and do the same until there are no more records in ORDERS that match the criteria of no PO_NR and no po created.


If I can do that, I think I can take care of the dates, etc.

Perhaps I'm trying to do it backwards?? If I could create the PO_NR in ORDERS, it would be easy. But, I can't use an auto increment field in a table with mutiple records matching the same criteria.

I've asked for help with this before and only got confused with the generous offers. So, I'm asking again.

I used to use the julien date for a PO number with the vendor ID appended to it. I created the PO number in both the ORDERS table and the PO_Header table. That worked fine unless the operator tried to place a second PO with the same vendor on the same day. then, the PO numbers were identical.

[Al Buchholz]

Re: Here I am again, begging for help

You should have a linking field that has nothing to do with the PO nbr...........

[Beltronics]

Re: Here I am again, begging for help

You are referring to the SET linking field?

[Al Buchholz]

Re: Here I am again, begging for help

You are referring to the SET linking field?

That worked fine unless the operator tried to place a second PO with the same vendor on the same day. then, the PO numbers were identical.

So you solved this problem by generating a po number and then picking the children to add to it?

It is difficult to follow the narrative of this.

Linking by vendor name is not too good... What will you do when the vendor is purchased or changes their business name?

Once you have a PO Nbr, don't you need to query for the child and then update those records with the PO that was just generated?

[dik_coleman]

Re: Here I am again, begging for help

For what it's worth......?????

I find it much easier to always use a character autoincrement id
variable rather than use a character or number variable that users
have access to. Names can be used as indices to search for a vendor or a
customer, but once the proper end is reached, I transfer to the
id number. All my sets are linked only by id numbers that cannot
be accessed or changed by users.

Just info for you
D

[Tim Kiebert]

Re: Here I am again, begging for help

Here is a sample that I think does what you want.

The 'utility' is on the code tab. The header table is empty and there are a few records in the order table. check them out first. then run the only script on the code tab.

The set uses the po_nr as the link.

Add some more order records and run the script again.

[Beltronics]

Re: Here I am again, begging for help

Wow, this has possibilities. Let me try and change the names you assigned to match the ones in my table and see if it works for me. It sure works in your example (there was never any doubt), but then you are the pro. I'm trying to follow your code as best I can.

[Beltronics]

Re: Here I am again, begging for help

Tim
I modified your code to fit my tables, ran the script, got no errors but it didn't post anything. I copied my tables to your application and tried to run it there with the same results. Can you look at it?

[Mike Wilson]

Re: Here I am again, begging for help

Jim,
Tim's code has the table field names of his table. You have to replace the field names for your table to get his code to run with your tables.

line 16: tord.query_create("TN",".not. Po_created","Vendor_name")
line 28: tpohd.order_date = date()
line 29: tpohd.Vendor_name = thisvendor
line 31: thisPOnum = tpohd.Po_nr
line 41: tord.Po_nr = thisPOnum

[Beltronics]

Re: Here I am again, begging for help

Thanks but, I thought I did that. Those field names in red (in your message) are my field names, not his. I'm not sure what the "TN" is in his code though so I left that alone. I compared his two tables with mine and change his field names to mine.

[Mike Wilson]

Re: Here I am again, begging for help

Jim,
I believe only the line 16 name was not correct, which would have stopped the code from running. I believe it was only as vendor and not vendor_name.

With that changed I do get an error running it. It ran for me when I took out the validation for the vendor_name field in the PO_header table and made line 16 order by "vender_name" instead of "vendor". The TN are query.order codes (look up <tbl>.query_create() in the docs and it tells you).

[Beltronics]

Re: Here I am again, begging for help

I see. I saw "Vendor" and thought it was referring to the Vendor Table.
Let me work with that for a bit.

I made that change. When I ran it, I got no error message but no results either.

More:
When I copied MY vendor table to Tim's application, overwriting his vendor table, then the code worked.
It didn't "set" the create_dtf and change_dtf field but I think my "can save record" field rule will take care of that.
At least I have something to work with now.

[Beltronics]

Re: Here I am again, begging for help

Here's the new attempt.

I need to work on posting the Create_dtf and change_dtf fields in the PO_Header table but I think this will work for me now.

I don't know why the field rule for record events, CanSaveRecord isn't working??

tc = table.current()
tc.change_dtf = date()

'used only if a *script* creates the record but doesn't include the creation date: (i.e., in case I mess up)
'it may not be the correct create_dt but at least it won't remain blank forever.
IF tc.create_dtf = {}
tc.create_dtf = date()
END IF

[Beltronics]

Re: Here I am again, begging for help

OK
This is working with the following exceptions.
Somehow, when I create a new PO_Header record,

I need to
put date() in the field called [create_dtf],
put date() in the field called [change_dtf],
put .f. in the field called [complete].

Everything else is working.

[Tim Kiebert]

Re: Here I am again, begging for help

Hi Jim,

I see Mike has been getting you sorted. I found exactly the same things as he did and did the same as he to get things working.

It is good to see we are making progress.

I need to
put date() in the field called [create_dtf],
put date() in the field called [change_dtf],
put .f. in the field called [complete].

the simple answer is to just add the setting of these fields in the header enter section of the code.

    tpohd.enter_begin()
    tpohd.order_date = date()
    tpohd.Vendor_name = thisvendor
    tpohd.create_dtf = date()
    tpohd.change_dtf = date()
    tpohd.complete = .f.
    tpohd.enter_end()

Another way which I tried and seems to work in this case is to add the optional logical parameter to the enter_begin function.

    tpohd.enter_begin([B][COLOR=Red].t.[/COLOR][/B])
    tpohd.order_date = date()
    tpohd.Vendor_name = thisvendor
    tpohd.enter_end()

This parameter is meant to enforce the UI level field rules but only if the table pointer is that of a currently open layout. See the help for .Enterbegin().

It seems though in this case to be triggering the default value field rules.

I have had mixed results with this parameter in the past and have gotten in the habit of doing all that needs doing within any code that adds records in a non user interface situation like this.