Kits-as invoice line items

[KathyWeeks]

I found this post from Jenny Lynch from 2004. I think she is doing what I need-but I don't understand where this code goes. I have an invoice with line items. Some of the Part numbers are for kits. Kits are made up of several part numbers. How can I let someone enter a kit number and then from that have it enter all the lines that make up the kit.

Here is her post that I found....

... I have written some xbasic code (see below) that creates item line records automatically if the user selects a "kit" which already has all stock lines selected - to save a lot of data entry. The form has an embedded browse for the stock lines and this is automatically filled in and displayed OK.

Code that 'auto-creates' the stock lines that belong to a kit :-

t3.fetch_first()
WHILE .NOT. t3.fetch_eof()
t2.enter_begin()
t2.docket=t1.docket
t2.stock=t3.stock
t2.quantity=t3.qty
t2.enter_end(.T.)
t3.fetch_next()
END WHILE

Could someone walk me thru this code and tell me where to put it and explain what its doing.

Any help would be greatly appreciated...thanks

[Walter]

Re: Kits-as invoice line items

Hello Kathy,

t3.fetch_first() 
WHILE .NOT. t3.fetch_eof() 
t2.enter_begin() 
t2.docket=t1.docket 
t2.stock=t3.stock 
t2.quantity=t3.qty 
t2.enter_end(.T.) 
t3.fetch_next() 
END WHILE  

From what I could gather from your post, as soon as you select "kit", that specific "kit"value will be used to filter all same "kit" numbers from a stock table or something and start data entry into the invoice details tbl.

The code would look something similar to this:

Dim SHARED t2 as P 'create a pointer variable for a table reference
Dim SHARED t3 as P 'another pointer.

t2 = table.open(Invoice_details,file_rw_shared) 'the invoice details tbl
t3 = table.open(table_containg_kits,file_rw_shared)' table that contains the kit records
query.description = "" 'desc of query
query.options = "n" ' create a temp query
query.order = ""
query.filter = "the_key_field_in_table_containing_kits = 'kits'" ' you would enter your filter expression here

qry = t3.query_create()'create the query based on the filter expression
Dim nrecs as n
nrecs = qry.records_get() ' return the number of records in the query you may want to check the value of nrecs, at this point it will return a number

'Now your code will start to enter records in the "invoice_details tbl
While .not. t3.eof() ' not to read past the last file in the filter
t2.enter_begin() 'start a new record entry in the "invoice_details tbl
t2.docket=t1.docket 'here I am not sure, this maybe the link between the invoice_header and invoice details tbl
t2.stock=t3.stock ' enter the stock value to t2
t2.quantity=t3.qty ' enter quantity value to t2
t2.enter_end(.T.) ' save the new entry in t2
t3.fetch_next()'fetch the next record in the query
END WHILE' end of loop

qry.drop() 'drop the query you created

t2.close()' close the opened table
t3.close()'same here

end 'code ends here

I am not sure where this code should run from, I assume it would be from a field on the parent form.
You will need to refine the code, I created this from memory, and you need to add your actual table field names and tables names.

But this should be more or less what the code should looks like.

Hope this help a bit
Walter

[KathyWeeks]

Re: Kits-as invoice line items

Does anyone have an example of this working? And would you be willing to upload it here so I can see how it's working? Even just a very simplified version would be fine. My kits are part of a set -they have Kit-Header and Kit-Items as the 2 component tables of the set.

I have a Kit Set
---->comprised of kit-header (contains the Kit_id_Number)
---------------> Kit-items (contains Kit_id_Number, part_number and cost)
I have an Invoice Set
---->comprised of Invoice-header
----------------->Invoice-items

When a user enters a kit ID in the Invoice-items field I want it to fill in the next few fields with the kit items that make up the kit.

I'm sorry I didn't get it from the post above-I'm trying to understand...

[KathyWeeks]

Re: Kits-as invoice line items

This is my interpretation of the post from above-feel free to point out the error of my ways... Thanks


dim shared t2 as p
dim shared t3 as p
t2=set.open_session(kit invoice set,file_rw_shared)
t3=set.open_session(kit set,file_rw_shared)
Query.description="kit query"
Query.options="n"
query.order=""
Query.filter="kit_id"
qry=t3.query_create()
dim nrec as n
nrecs=qry.records_get()
while.not.t3eof()
t2.enter_begin()
t2.inv_nu=t3.inv_nu
t2.part_number=t3.part_number
t2.cost=t3.cost
t2.enter.end(.t.)
t3.fetch_next()

qry.drop()
t2.close()
t3.close()
end

[Jenny Lynch]

Re: Kits-as invoice line items

Hi Kathy

my code for the kit stock is attached to the "On Depart" event on the Kit Code field so that after they enter the kit code, the browse below it is automatically filled in with the stock codes and quantities.

Here is the current xbasic code : (the table.current is the docket header)

dim t1 as p
dim t2 as p
dim t3 as p
dim qry as p
dim mesg as c
t1=table.current()
t2=table.open("qstock.dbf")

t3=table.open("kitstock")

if t1.kit <> "" then
query.filter="kit='"+t1.kit+"'"
query.order=""
qry=t3.query_create()
nrecs=qry.records_get()
select
case nrecs>=1
t3.fetch_first()
WHILE .NOT. t3.fetch_eof()
t2.enter_begin()
t2.docket=t1.docket
t2.stock=t3.stock
t2.quantity=t3.qty
t2.enter_end(.T.)
topparent.resynch()
t3.fetch_next()
END WHILE
end select
end if


and this code is follwed by an action script to refresh the display.


Hope this helps

regards
Jenny Lynch

[KathyWeeks]

Re: Kits-as invoice line items

Would someone take a look at this code and see what might be the problem?
It returns this error:
Script: On Depart line:18
qry=t3.query_create()
Expected Value

here is the code:
Date Created: 07-Nov-2006 09:26:20 AM
'Last Updated: 07-Nov-2006 10:39:32 AM
'Created By : KMW
'Updated By : KMW
dim t1 as p
dim t2 as p
dim t3 as p
dim qry as p
dim mesg as c
t1=table.current()
t2=table.open("kit-invoice-items")
t3=table.open("kit-line items")

if t1.kit_number<>"" then
query.filter="Kit_id="'"+t1.kit_number+"'"
query.order="recno()"
query.options=""
qry=t3.query_create()
nrecs=qry.records_get()
select
case nrecs>=1
t3.fetch_first()
WHILE .NOT. t3.fetch_eof()
t2.enter_begin()
t2.kit_invoice_number=t1.kit_invoice_number
t2.part_number=t3.part_number
t2.cost=t3.cost
t2.enter_end(.T.)
topparent.resynch()
t3.fetch_next()
END WHILE
end select
end if

[Al Buchholz]

Re: Kits-as invoice line items

Kathy

To debug this, we need to see the table(s) involved also. I'd guess that a field name is type wrong...

[Stan Mathews]

Re: Kits-as invoice line items

Mismatched quotes. Try

query.filter="Kit_id='"+t1.kit_number+"'"

You had

query.filter="Kit_id=[B][COLOR="Red"][SIZE="5"]"'"[/SIZE][/COLOR][/B]+t1.kit_number+"'"

In this line the second double quote effectively ends the line, the first single quote is interpreted as beginning a comment, and the rest is history.

Also, I hope you don't have tables named

kit-invoice-items
kit-line items

From the documentation...

Alpha Five recommends that table names, field names, and paths to your files start with a letter (A-Z or a-z) and be composed of letters, numbers (0-9), and underscore (_) characters. Do not use tilde "~" characters.

Thus spaces and dashes (also known as "minus signs") are counterindicated.

[KathyWeeks]

Re: Kits-as invoice line items

Thank you Stan-That did the trick
Would you tell me what the ' mean?

Translate this to laymans terms:
query.filter="Kit_id='"+t1.kit_number+"'"

[Stan Mathews]

Re: Kits-as invoice line items

Thank you Stan-That did the trick
Would you tell me what the ' mean?

Translate this to laymans terms:
query.filter="Kit_id='"+t1.kit_number+"'"

Since you are "passing" the contents of t1.kit_number to the quoted filter expression you have to "break out" of the quoted expression and quote what you are passing in.

Consider
query.filter="Kit_id = 'abcde' "
where the abcde character value must be quoted in the expression. To pass in the contents of t1.kit_number you must stop the original expression with a quote
query.filter="Kit_id = ' "
concatenate the contents of t1.kit_number
query.filter="Kit_id = ' "+t1.kit_number
then concatenate the rest of the original expression
query.filter="Kit_id = ' "+t1.kit_number+" ' "

I left spaces between each ' and " for visual clarity. In practice you would not anymore than you would use

query.filter="Kit_id = ' abcde ' "

In a single term filter like this you can use the equivalent

query.filter="Kit_id = "+quote(t1.kit_number)

and let Alpha do the hard work.

In a multiterm expression you can use the same but still have to resume concatenation after like

query.filter="Kit_id = "+quote(t1.kit_number) +".and. someother field = 7"

[KathyWeeks]

Re: Kits-as invoice line items

Someday this will be as easy as reading a sentence... (I hope)

Thank you for your help

[Stan Mathews]

Re: Kits-as invoice line items

Someday this will be as easy as reading a sentence... (I hope)

Thank you for your help

Does any of it make sense? Have you ever constructed a

query.filter = "fieldname = 'abc'"

type expression?

I'll be glad to elaborate if you think it would help.

Have you seen Writing Expressions?

Download my attachment, unzip the table to a new directory, add the table to your database. Think of how you would create a filter for a report...

kit_number = "abcde"

In a script the above must be quoted to have a filter.

query.filter = "kit_number = "abcde" "

but we can't have quotes within quotes so we have to change to

query.filter = "kit_number = 'abcde' "

Then we have to find a way to make this refer to an outside value such as a table field value, a variable, etc.


Try this in the interactive editor.

tbl = table.open("mykit")
? tbl.Kit_number
= "abcde"

query.filter = "Kit_id = tbl.Kit_number"
? query.filter
= "Kit_id = tbl.Kit_number"
'here we just get the letters tbl.Kit_number, not the contents

query.filter = "Kit_id = "+tbl.Kit_number
? query.filter
= "Kit_id = abcde"
'this looks ok but since the abcde must be quoted, it won't work

query.filter = "Kit_id = '"+tbl.Kit_number+"' "
? query.filter
= "Kit_id = 'abcde' "
'desired final expression
tbl.close()

[KathyWeeks]

Re: Kits-as invoice line items

It makes a little sense. It just takes time to learn it all. I will take a look at "writing expressions".

I guess I spoke too soon on it working.... As I get to looking at it it adds the Part number but it doesn't bring back the cost. Is there something that I need to do to tell it is numeric?

[Stan Mathews]

Re: Kits-as invoice line items

It makes a little sense. It just takes time to learn it all. I will take a look at "writing expressions".

I guess I spoke too soon on it working.... As I get to looking at it it adds the Part number but it doesn't bring back the cost. Is there something that I need to do to tell it is numeric?

I added a few more words of explanation to my previous post.

Now, are you saying that

t2.enter_begin()
t2.kit_invoice_number=t1.kit_invoice_number
t2.part_number=t3.part_number
t2.cost=t3.cost
t2.enter_end(.T.)

seems to be working in that the
t2.kit_invoice_number gets populated and the
t2.part_number gets populated but the
t2.cost is staying blank

????

[Stan Mathews]

Re: Kits-as invoice line items

Does any of it make sense? Have you ever constructed a

query.filter = "fieldname = 'abc'"

type expression?

I'll be glad to elaborate if you think it would help.

Have you seen Writing Expressions?

Download my attachment, unzip the table to a new directory, add the table to your database. Think of how you would create a filter for a report...

kit_number = "abcde"

In a script the above must be quoted to have a filter.

query.filter = "kit_number = "abcde" "

but we can't have quotes within quotes so we have to change to

query.filter = "kit_number = 'abcde' "

Then we have to find a way to make this refer to an outside value such as a table field value, a variable, etc.


Try this in the interactive editor.

tbl = table.open("mykit")
? tbl.Kit_number
= "abcde"

query.filter = "Kit_id = tbl.Kit_number"
? query.filter
= "Kit_id = tbl.Kit_number"
'here we just get the letters tbl.Kit_number, not the contents

query.filter = "Kit_id = "+tbl.Kit_number
? query.filter
= "Kit_id = abcde"
'this looks ok but since the abcde must be quoted, it won't work

query.filter = "Kit_id = '"+tbl.Kit_number+"' "
? query.filter
= "Kit_id = 'abcde' "
'desired final expression
tbl.close()

Forgot to attach, will attach tomorrow.