Need to enter data from a form to an external table

[Classick]

Hello,

i have the current situaiton.

Parent form with a number field [price] on it.

the form is based on a 1:n set and also has an embedded browse on it.

problem is the column i wish to "update" isnt in the embedded browse, so i cannot just use the genie.

Can someone help me with how to enter data from a form into an external table?

I have the following so far.

dim nrecs as N
dim avgprice as N
'Get 'Value' property of 'Joborder_id' in Form 'CSIStock_Receive' .
DIM GLOBAL JOID AS c
JOID = topparent:Joborder_id.value

nrecs=count(Csistock->Buy_Price,GRP->JOB_ORDERS)
'Get 'Value' property of 'Price' in Form 'CSIStock_Receive' .
DIM GLOBAL BulkPrice AS n
BulkPrice = topparent:Price.value

AvgPrice = BulkPrice / nrecs

Where Job Orders is the parent table, csistock is the child table (1:N)

and i am trying to average the user entered price across the # of child records, then enter that # into ALL of the child table's fields (Buy_Price), that is all the child records associated with that JOID, should have the value $7 posted if $49 is entered into the parent field (price) and there are 7 child records.

Buy_Price is NOT on the embedded broswe, but it is in the table.

I have set the linking key ( Job order ID) to a variable (JOID) so i can reference it in a custom query if one needs to be run.

any help is much appreciated.

dave

[Mike Wilson]

Re: Need to enter data from a form to an external table

Dave,

Hi. I read your post and there is alot going on that I'm having a hard time capturing from your post. First you wrote over and over again the set Parent:Child reationship is 1:N. I must be missng something but what is that? (One:One,ie 1:1) or is it 1:M (One to Many; 1:M).

Can someone help me with how to enter data from a form into an external table?

This is very doable.

and i am trying to average the user entered price across the # of child records, then enter that # into ALL of the child table's fields (Buy_Price), that is all the child records associated with that JOID, should have the value $7 posted if $49 is entered into the parent field (price) and there are 7 child records.

Wow! Dave, at best this I can resolve this to being confusing and complicated. Can you break it down for us a little, and approach this with smaller bites?

Mike W

[Classick]

Re: Need to enter data from a form to an external table

Heh, sorry for the confusion Mike. lemme give it a 2nd try.

I have a Form based on a One to Many set ( 1:M, my finger must have slipped)

The parent of the form is the table [Job_Orders]
The child is [csistock],
they are linked by the field "joborder_id"'

On the parent form, there is a field "Price" that has a button beneath it.
the button uses the onPush event to run the following operation

1) Take the number that is entered in the "Price" field, and divide it by the count of the records in the child table ( that match the current Joborder_id) and store the value in a variable called "AvgPrice"

2) update every "buy-Price" field in the child table (with the matching joborder_id) with the value from AvgPrice.

now, there is a browse on the page, showing a few of the details from the child table, but the field that i am updating isnt displayed.

I hope that clears some things up, if not, ill post images tomorrow.

[Tom Cone Jr]

Re: Need to enter data from a form to an external table

Actually, I think 1:N is better than 1:M. Since to me "N" suggests "any" "N"umber of linked child table records. -- tom

[Classick]

Re: Need to enter data from a form to an external table

regardless of the terminology... the problem remains

i have used a workaround but adding the BuyPrice field to the embedded browse.... but i would really rather it not be.

[Stan Mathews]

Re: Need to enter data from a form to an external table

regardless of the terminology... the problem remains

i have used a workaround but adding the BuyPrice field to the embedded browse.... but i would really rather it not be.

Not sure I understand all that's been said but it seems you need the buyprice field in the browse but don't want it visible? Set the column width to be very narrow.

[Mike Wilson]

Re: Need to enter data from a form to an external table

Dave,

OK, you have the average price that you wish to enter to all the records matching the joborder_id in a variable Avgprice. First get the joborder_id into variable joid. To update the child table, place the following script in an Update button OnPush event on the form and place the following into an Action Script>Inline Xbasic:

dim tbl as p

tbl=table.open("csistock")
query.filter = "joborder_id = "+Var->joid   [COLOR="Red"]'if joid is numeric[/COLOR]
query.filter = "joborder_id = "+s_quote(Var->joid)  [COLOR="Red"]'if joid is Character[/COLOR]
query.order = ""
xi = tbl.query_create()

tbl.fetch_first()
while  .not. tbl.fetch_eof()
tbl.change_begin()
   tbl.buyprice = Var->Avgprice
tbl.change_end(.t.)
tbl.fetch_next()
end while
xi.drop()
tbl.close()

This should update the child table for all the records having that joborder_id. Be aware that if you have the buyprice field in the Browse, after running this script you will not see the change in the Browse (Browse refresh issue).

Hope this is what you needed and helps.

Mike W

[G Gabriel]

Re: Need to enter data from a form to an external table

Dave:

Make buyprice a calc field:

buyprice=parent->price/tablecount("Job_Orders", grp->JOB_ORDERS)

[Mike Wilson]

Re: Need to enter data from a form to an external table

Dave:

Make buyprice a calc field:

buyprice=parent->price/tablecount("Job_Orders", grp->JOB_ORDERS)

Gabe,
Could you show me how that might work, where it would be positioned in the Quote:Invoice (1:M) set with parent field> Price and Child field> Buyprice of this example database. Thanks.

Mike w

[G Gabriel]

Re: Need to enter data from a form to an external table

Mike:
Per your request.

[Mike Wilson]

Re: Need to enter data from a form to an external table

Gabe,
Thanks for the effort, but it isn't working for me. The Price field is frozen, the Buy_Price is eratic, and the database is unstable (freezes).
The table_change() to the child approach worked well and I posted it for Dave to see. The script is in the OnSave event of the Price field and also in the Write BuyPrice button (redundant).

Mike W

[G Gabriel]

Re: Need to enter data from a form to an external table

Mike:
Did you open the zip file I attached??
I eliminated events/ field rules from your file. If you keep them and add the calc field, you will have these conflicts.

[Classick]

Re: Need to enter data from a form to an external table

Hey guys, thanks for posting some stuff for me to check out... i will test them out on tuesday when i get back to work.

Thanks again for the effort. Folks on this board have been so great.