Trying to figure out how to create temp table based on filters in the parent and data in the child.

[preston2]

Well as if the subject made absolutely no sense.

I have a set wo.set

The parent is wo_header with a one to many child I need to pull a summary total from.

Using a operation as a guidance for the xbasic only allows me to pull data from the child because if I use the set as source I am not allowed to select any of the one to many tables.

If I select the one to many table as the source then I cannot use filters based on data in the parent.

So bottom line is I need to test 2 logical fields in the parent and then only pull summary totals from the child table if the parent logical are met.

What I have now works but pulls all summary data from the child. I need to only summarize records in the child where the logical field "Ordered" = .F. and the logical field "Invoice" = .T.

summarize_db_name = filename_decode("[PathAlias.ADB_Path]\order_items.DBF")

tbl = table.open("wo_items")'this is a one to many child table in the wo.set
ON ERROR GOTO ERROR0301201613210257

sum.db = summarize_db_name
sum.db_dd_delete = .F.
sum.result_overwrite = .T.
sum.order = "STYLE+COLOR"
sum.filter = ""
sum.options = "I"
sum.fields = 12
sum.field1 = "STYLE"
sum.code1 = 0
sum.field2 = "COLOR"
sum.code2 = 0
sum.field3 = "XS"
sum.code3 = 1
sum.field4 = "S"
sum.code4 = 1
sum.field5 = "M"
sum.code5 = 1
sum.field6 = "L"
sum.code6 = 1
sum.field7 = "XL"
sum.code7 = 1
sum.field8 = "X2"
sum.code8 = 1
sum.field9 = "X3"
sum.code9 = 1
sum.field10 = "X4"
sum.code10 = 1
sum.field11 = "X5"
sum.code11 = 1
sum.field12 = "X6"
sum.code12 = 1

tbl.summarize()


GOTO CONTINUE0301201613210257
ERROR0301201613210257:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Summarize Operation"+crlf()+error_text_get())
END
CONTINUE0301201613210257:
tbl.close()
'Add the table just created by the Summarize operation to the Database..
file_add_to_db(summarize_db_name)

Report off the order_items.dbf looks like this.

[Ray in Capetown]

Re: Trying to figure out how to create temp table based on filters in the parent and data in the child.

A number of ways come to mind.
The easiest would be to summarize from a mapped table :- wo_items with the two fields from wo_header

[preston2]

Re: Trying to figure out how to create temp table based on filters in the parent and data in the child.

Not sure if what you said is what I got to work so far.

Basically I am now creating one temp table based off the filters and then creating the final temp table based off the code above.

Now granted I could have gotten the results into a report real easy just using a query and not even using temp tables but I need to do some more stuff that requires the data is pulled out of the tables in the set.

It took code from 3 different operations to get the main part of the results I wanted right.

Now I need to give them an option to check in house inventory and deduct any that match from the order list and at the same time remove any used from inventory.

To do this it must check the garment Style, Color and Size of what they need to produce the orders and then check the inventory to see if they have any already in stock. Then print out a report of what to order and what to pull from stock.

And then after all that it must go back and mark all the records as items_ordered

Good thing I love math.

[MoGrace]

Re: Trying to figure out how to create temp table based on filters in the parent and data in the child.

What if you copied records from the set into a temp table and then summarized the temp table? That way your fields from the parent would be included and you could construct a filter.

[preston2]

Re: Trying to figure out how to create temp table based on filters in the parent and data in the child.

That is basically what I ended up doing Robin.

Copy the records from the set that meet the filter into a temp table.
Then delete all blank records in the temp table if any
Then summarize the records in that temp table and put them in another temp table. This table will contain the items they need to order.

I have that all working now.

Next I need to allow them to check in house inventory and subtract any matching Style/Color/Size that match.
Once I have that I can make a report telling them what they need to order and what they need to pull from stock.