Getting fields in a record into a drop down list.

[preston2]

Ok, trying to figure out the best way to tackle this.

I have a child table in a set that stores user entered sizes.

Matrix_Id,16,0
Matrix,C,20,0
Dsize0,C,12,0
Dsize1,C,12,0
Dsize2,C,12,0
Dsize3,C,12,0
Dsize4,C,12,0
Dsize5,C,12,0
Dsize6,C,12,0
Dsize7,C,12,0
Dsize8,C,12,0
Dsize9,C,12,0
Dsize10,C,12,0
Dsize11,C,12,0
Dsize12,C,12,0
Dsize13,C,12,0
Dsize14,C,12,0

What I need to do is get the values of Dsize0 through Dsize14 plus the Matrix_ID to link it into another table that would have 15 records in it associated with the Matrix_ID that could then be used for a table drop down list to select from when entering data into fields in another table.

The selection table would have these fields.
Matrix_Id,16,0
Dsize,C,12,0

This selection table would need the records in it rewritten anytime the user selected a different Matrix to use.

I think I have an idea on how to do it so I am just bouncing the question around here to basically see if I will be going about this right.

On the invoice form there is a filed where the select which pricing matrix they use.

My thoughts are to use the On Change event to fire a script that would zap the table that would be used for drop down selection in other places on the invoice and then using a loop to write the data from the child table in the Matrix set to the selection table.

[MoGrace]

Re: Getting fields in a record into a drop down list.

Hi Preston,
If you populate the other table with multiple lookups for each of these fields, it is going to be incredibly slow.

An Xdialog might be a better way to go. You could have a variable that selects the MatrixID and then based on that value populate a list filtered with the Dsizes you want to see. Set a shared variable with the value the user selects and populate your table field when the user selects OK.

Since your Dsize fields are numbered, you could populate a list first with an array that fetches thru the table filtered by the MatrixID. How about a small sample of your data - 5 or 6 Matrix records should be enough to set up an example.

[Stan Mathews]

Re: Getting fields in a record into a drop down list.

Why not do a field rule table lookup tied to (filtered on) the current matrix id?

Or a form list box.

mszmast.zipmsizes.zip

[MoGrace]

Re: Getting fields in a record into a drop down list.

Stan,
How would he select from the 15 fields? He'd still need 15 different lookups - one for each Dsize

[Stan Mathews]

Re: Getting fields in a record into a drop down list.

Amended my original suggestion.

The problem is the denormalized size table. should be one record per matrix_id and size.

[MoGrace]

Re: Getting fields in a record into a drop down list.

I think he wants to create something like this from the matrix table records:

dsize_lu.jpg

Edit: Looks like you beat me to it!

Now all we need is the structure of the "other table" he wants the lookup FRUL for.

[preston2]

Re: Getting fields in a record into a drop down list.

Ok, in the ExampleMat file there is the full matrix set and tables. There is also an example wo set.

In the Matrix on the DTG or Transfers tab, Dsize1A and Dsize1B get alltrim'ed and then combined into Dsize1. So Dsize1A with a value of 5 and Dsize1B with a value of 5 would result in Dsize1 having a char value of " 5 x 5 "

Dsize0 is always going to have the value of "" so that the first option in the drop down would be blank.

[preston2]

Re: Getting fields in a record into a drop down list.

Got it. May not be the cleanest way to get it done but it works.

dim vc_dsize as c
dim vc_dsize0 as c
dim vc_dsize1 as c
dim vc_dsize2 as c
dim vc_dsize3 as c
dim vc_dsize4 as c
dim vc_dsize5 as c
dim vc_dsize6 as c
dim vc_dsize7 as c
dim vc_dsize8 as c
dim vc_dsize9 as c
dim vc_dsize10 as c
dim vc_dsize11 as c
dim vc_dsize12 as c
dim vc_dsize13 as c
dim vc_dsize14 as c
dim vn_matrix_id as n


vc_dsize0 = ""
vc_dsize1 = dtg_size->dsize1
vc_dsize2 = dtg_size->dsize2
vc_dsize3 = dtg_size->dsize3
vc_dsize4 = dtg_size->dsize4
vc_dsize5 = dtg_size->dsize5
vc_dsize6 = dtg_size->dsize6
vc_dsize7 = dtg_size->dsize7
vc_dsize8 = dtg_size->dsize8
vc_dsize9 = dtg_size->dsize9
vc_dsize10 = dtg_size->dsize10
vc_dsize11 = dtg_size->dsize11
vc_dsize12 = dtg_size->dsize12
vc_dsize13 = dtg_size->dsize13
vc_dsize14 = dtg_size->dsize14
vn_matrix_id = matrix->matrix_id

a_tbl = table.open("dsize")
ON ERROR GOTO ERROR01062015202825152


a_tbl.delete_range(".T.")


GOTO CONTINUE01062015202825152
ERROR01062015202825152:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Mark/Un-mark/Delete Operation"+crlf()+error_text_get())
END
CONTINUE01062015202825152:
a_tbl.close()'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
	if topparent.Class() = "form" .or. topparent.class() = "browse" then
		topparent.Refresh_layout()
	end if
end if

'Open the table that you want to write records to...
dim tbl as p 
tbl = table.open("dsize",FILE_RW_SHARED)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize0,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize1,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize2,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize3,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize4,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize5,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize6,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize7,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize8,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize9,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize10,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize11,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize12,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize13,"Character")
tbl.enter_end(.t.)

tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(vc_dsize14,"Character")
tbl.enter_end(.t.)

tbl.close()

[MoGrace]

Re: Getting fields in a record into a drop down list.

Hi Preston,
In your dtg_size table the dsize## fields are filled with calculations based on 2 other field values. If those calculated values never change once created then your dsize table will be static. However if you create the dsize lookup table with the A and B fields, you can make the dsize calc field dynamic for your lookup table. Then your script can update the A and B values from whatever source they come from.

DSIZE.DBF

matrixid
ds_name
ds_a
ds_b
ds_val calc frul = IF(DS_A="","",ALLTRIM(DS_A) +" X "+ALLTRIM(DS_B))

When defining the lookup, you filter on matching Matrixid and display the ds_name and ds_val fields.

dsize_lookup.jpg

[preston2]

Re: Getting fields in a record into a drop down list.

Robin,

The Dsize# fields can change and are not static. The end user can change the resulting Dsize# at any time by change the values in the Dzise#A and Dsize#B fields.

The script I posted above clears the Dsize.dbf table out and then recreates it based on the current Dsize# field values in the Dtg_size.dbf table that is a child of the Matrix table.

I could probaly get away without even putting the Matrix_ID field in the Dsize table as linking to the correct record in the Dtg_Size table is done in the set structure.

The result is that the Dsize table only has the values from one Matix_Id record at a time.

Someone with a real slow computer may notice a 1/2 second delay or so as the Dsize table is cleared and rewritten but on my computer it happens instantly.

[MoGrace]

Re: Getting fields in a record into a drop down list.

Preston,
My thoughts were following on Stan's post #5. But not knowing how, when or why this data is changed in the dtg_size table, left me with guessing. I am sure your table structures are necessary for this matrix style app - which is much too complicated for me!

However your script that populates the dsize table, still suggests that an array might be useful - I will bow to Stan on that one...

[Stan Mathews]

Re: Getting fields in a record into a drop down list.

You could shorten your script a bit.

dim vc_dsize as c
dim vn_matrix_id as n
for qx = 0 to 14
	evaluate_template("dim vc_dsize"+alltrim(str(qx)) +" as c")
	eval("vc_dsize"+alltrim(str(qx))) = eval("dtg_size->dsize"+alltrim(str(qx)))
next qx
vc_dsize0 = ""
vn_matrix_id = matrix->matrix_id

a_tbl = table.open("dsize")
ON ERROR GOTO ERROR01062015202825152
a_tbl.delete_range(".T.")


GOTO CONTINUE01062015202825152
ERROR01062015202825152:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Mark/Un-mark/Delete Operation"+crlf()+error_text_get())
END
CONTINUE01062015202825152:
a_tbl.close()'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
	if topparent.Class() = "form" .or. topparent.class() = "browse" then
		topparent.Refresh_layout()
	end if
end if

'Open the table that you want to write records to...
dim tbl as p 
tbl = table.open("dsize",FILE_RW_SHARED)

for qz = 0 to 14
tbl.enter_begin()
tbl.MATRIX_ID = convert_type(vn_matrix_id,"Numeric")
tbl.DSIZE = convert_type(eval("vc_dsize"+alltrim(str(qz))),"Character")
tbl.enter_end(.t.)
next

tbl.close()

[preston2]

Re: Getting fields in a record into a drop down list.

Stan,

I am no where near as good as you are with xbasic.

Mine works but is long winded. When I first looked at the streamlined version you posted I said to myself "WTF" but after looking at it I think I see how it works.

Will give it a try.

And Thanks.

[Stan Mathews]

Re: Getting fields in a record into a drop down list.

I don't know if I'm particularly good at it or not. I just beat at the keyboard until it does what I want. Persistence != proficiency.

My code is the same as yours but I took the repetitive parts and wrapped them in loops so that the code writes itself. Neither bettor nor worse, just more compact. The one advantage I've found to doing it this way is that if you decide you need more or fewer sizes, you just adjust the loop counter, not add or delete code lines.

If you really want to get tricky, have your code count the size fields in dtg_size and use that count in the code. That way you just edit the table structure and the code adjusts itself.

tbl = table.open("dtg_size")
all_flds = tbl.field_name_get()
? all_flds
= matrix_id
dsize0
dsize1
dsize2

size_flds = ""
for each foo in all_flds
#size_flds = size_flds+if(left(foo.value,5) = "dsize",foo.value+crlf(),"")
#next
? size_flds [COLOR="#0000FF"]'from my abbreviated table of the same name[/COLOR]
= dsize0
dsize1
dsize2

? line_count(size_flds)
= 3

tbl.close()

[preston2]

Re: Getting fields in a record into a drop down list.

Stan,

I beat on the keyboard until I get something working too.

I am just not as versed in some of the code tricks and short cuts.

I had originally tried a loop but could not get the right code inside the loop to actually write the table records right.

But I have found that when I get stuck, if I come here and post I seem to figure things. Sometimes with the gracious help of others here and sometimes just having the light bulb turn on.


And as always. Thanks to all.