Lookup values from a comma seperated field

[den1s]

Re: Lookup values from a comma seperated field

Stan,

I have implemented your suggestion and it works just fine. I have modified your code pointers to match my database. As you will see i have a seperate table that holds the values for the lookup.

tbl = table.current()
strlist = tbl.AREA_DISCIPLINE_IDS
tbl.AREA_DISCIPLINE = left(strtran(*for_each(foo,lookup("tbl_diciplines","dicipline_id = "+quote(foo),"alltrim(full_pj_desc)"),strtran(strlist,",",crlf())),crlf(),","),-1)

Unfortunately I have another problem with the maintainability of the values. If a user changes a value in tbl_diciplines, it does not change the lookup value in the current table. I have tried creating a new field that i could run as a calculated field and change the value say from .T. to .F. so that each record updates, but the lookup value remains unchanged.

If however, you change any value from any field within the record manually, the lookup value does change to the most recent value in tbl_diciplines.

Eg.

tbl_diciplines
01-Tea
02-Coffee
03-Bread
04_Milk

Current_Table
id--------location-------type------Tea
id--------location-------type------Coffee
id--------location-------type------Bread

If a user changes the descriptive value of Tea to Tee within tbl_disciplines the value in the current table does not change. If however, you update any part of the record in the current table for Tea, the lookup kicks in a changes the value to the new value.

Do you know the best way to maintain the accuracy of the data in the current table, should anyone change the values in the lookup table?

Thanks,

Denis

[Mike Wilson]

Re: Lookup values from a comma seperated field

run an update from the parent table OnSaveRecord event that changes all the values in the linked child table?

dim t as P
dim vid as C
t=table.current()
vid = t.id

dim tt as P
tt=table.open("current_table")
query.filter="id="+quote(vid)
query.order=""
query.option="T"
xi=tt.query_create()
t.fetch_first()
while .not. tt.fetch_eof()
	tt.change_begin()
	tt.name=t.name
	tt.change_end(.t.)
tt.fetch_next()
end while
xi.drop()
tt.close()

[den1s]

Re: Lookup values from a comma seperated field

Mike,

Thank you for responding.

This is what i have done;

Amended your code to fit my database, but not sure if I have placed all the things are in the right places. There is no update to the table made.

Let me explain;

I have placed this code in the OnSaveRecord section of tbl_diciplines.

dim t as P
dim vid as C
t=table.current()
vid = t.dicipline_id - This is the ID in tbl_diciplines

dim tt as P
tt=table.open("tbl_issues_log") - The table that needs to be updated
query.filter="area_discipline_ids="+quote(vid) - The field in tbl_issues_log that contains all the id's in a memo field - 001,002,432,456....
query.order=""
query.option="T"
xi=tt.query_create()
t.fetch_first()
while .not. tt.fetch_eof()
tt.change_begin()
tt.Area_discipline=t.full_pj_desc - field in tbl_issues_log = field in tbl_diciplines
tt.change_end(.t.)
tt.fetch_next()
end while
xi.drop()
tt.close()

Denis

[Mike Wilson]

Re: Lookup values from a comma seperated field

Denis,
I'm guessing that:

vid = t.dicipline_id - This is the ID in tbl_diciplines

generates a variable value of a single id. Since the field you are pointing at

query.filter="area_discipline_ids="+quote(vid) - The field in tbl_issues_log that contains all the id's in a memo field - 001,002,432,456....

to filter contains many id's, the 'equal' will not work. Try a filter with a contains operator or function.

[den1s]

Re: Lookup values from a comma seperated field

Hi,

I have been away on my holiday, so sorry for any reply delays.

Stan has provided me with a nice bit of code;

tbl = table.current()
strlist = tbl.AREA_DISCIPLINE_IDS
tbl.AREA_DISCIPLINE = left(strtran(*for_each(foo,lookup("tbl_diciplines","dicipline_id = "+quote(foo),"alltrim(full_pj_desc)"),strtran(strlist,",",crlf())),crlf(),","),-1)

When i use this i have noticed the following;

If you have only one 'AREA_DISCIPLINE_IDS' entered in the tbl_issues_log->AREA_DISCIPLINE_IDS field and then make a change within the tbl_diciplines->full_pj_desc field. Then go back to the tbl_issues_log table, the change is made on entry into the table. If you have more than one 'AREA_DISCIPLINE_IDS' in tbl_issues_log->AREA_DISCIPLINE_IDS field and do the same thing again, the change isn't made. :-( The only way to make the change is to change another field i that record which forces the lookup to re-run.

Is it possible to get the automatic change function to work with two or more ID's?

If this was to work then my problems are over.

Please help.

Thanks in advance.

Denis

[den1s]

Re: Lookup values from a comma seperated field

Mike,

Thank you for your code.

I cannot seem to get my head around making this work for me. I wonder if you could further elaborate on the detail. In particular, how would i get this to work in my app taking into consideration the structure.

Any further help would be greatly appreciated.

Denis

[den1s]

Re: Lookup values from a comma seperated field

Hi All,

I have been playing around with this all morning.

I have two tables, tbl_issues_log and tbl_diciplines. tbl_diciplines has two fields called dicipline_id and full_pj_desc. tbl_issues_log has two fields, area_discipline_ids and Area_discipline. The tbl_issues_log field rules/events has the following code in the CanSaveRecord event.

tbl = table.current()
strlist = tbl.AREA_DISCIPLINE_IDS
tbl.AREA_DISCIPLINE = left(strtran(*for_each(foo,lookup("tbl_diciplines","dicipline_id = "+quote(foo),"alltrim(full_pj_desc)"),strtran(strlist,",",crlf())),crlf(),","),-1)

The tbl_diciplines tables is populated in the following way;

dicipline_id-------full-pj_desc
101--------------Tea
102--------------Coffee
103--------------Milk

The tbl_issues_log is populated in the following way;

area_discipline_ids--------area_discipline
102----------------------Coffee
102,101,103--------------Coffee,Tea,Milk

When you type in the dicipline_id directly in the tbl_issues_log->area_discipline_ids field the area_discipline field will perform the lookup and populate the field with the correct information.

For example, If you change the text for the tbl_diciplines->dicipline_id 101 to Chocolate and then open the tbl_issues_log, all the 101 entries change to chocolate. Good. If however you change item 102 to Biscuit and go into the tbl_issues_log item 102 does not change.

It appears to only recognise the first entry in the tbl_diciplines, the second entry is ignored.

Does anyone know how i could change the above code to work?

Thanks,

Denis

[den1s]

Re: Lookup values from a comma seperated field

I have now implemented Mike's code and it works with single entry items only. Can anyone take a look at the attached file and see if the code can be amended to change entries with multiple comma separated values.

[Mike Wilson]

Re: Lookup values from a comma seperated field

Denis,
I worked up an example for the info you gave, not the example you posted. See if this does what you need. I will look at the other later.

Later:
You can place the discipline_update() code on the OnSaveRecord field event, too. HOWEVER. The table name is DICipline in your example. The table and the fields are all DISCipline in mine. You will have to make sure these are all correct. I can't get Stan's code update the field with a change, right now. Maybe I'll look later.

[den1s]

Re: Lookup values from a comma seperated field

Mike,

So glad you can help. Thank you.

I assume this works at table level. If that is right, i changed a value in the diciplines table e.g. Tea to Bread, but it didnt reflect the change in the issues log.

I then ran the code manually, still no change.

Am i doing something wrong?

Thanks,

Denis

[Mike Wilson]

Re: Lookup values from a comma seperated field

No, it is not set up to work at the table level. I have two forms in the example. Both use form level events to trigger the UDFs. From your first post:

In one of my tables i have a memo field that contains items selected from a checkbox list. When a user selects more than one item from the checkbox list, the field is populated with the ID_No(s) seperated by a comma.

I'm seeing a form in this picture. There will also have to be a differentiation between entering new records and editing an existing record. So the error_table entry I'm seeing as a form event. The other can be a field rule event.

Later: I think I had it backward. It is the discipline table that needs to have the distinction of change vs enter mode. A change in the discipline descriptor (tea to teas) would need an update but a new discipline would have nothing to update.

[den1s]

Re: Lookup values from a comma seperated field

Mike,

I really appreciate the time you have spent on this issue.

I did try to use your form but got an error (see attached). I am not familar with desktop applications, so i am not sure how this is going to help me. I do not know where to look for the form generated code on desktop forms.

Again, thank you for your help, but unfortunately i really need something that i am familar with, otherwise am am left lost :-(

I just wish there was an avanced XBasic course i could go on to give me the extra knowledge i need.

Denis

[Mike Wilson]

Re: Lookup values from a comma seperated field

Denis,
OK, sorry, my bad.

The action to update all the values in the issue_log when a change is made in the tbl_disciplines table can happen from the field rule record event OnSaveRecord. The attached runs a UDF named discipline_update() called from the OnSaveRecord event of the tbl_discipline table.

The action to Enter or Change choices in the issue_log using a check box-list box, like you stated, can't be called from a table field rule event that I have been able to figure out. I could not find a way to have it call a UDF that writes to the table that called the UDF without generating a conflict. The action of calling a checkbox must happen somewhere in your application that is not a direct address to the table. This I am depicting as being the issue log form. The UDF that opens the checkboxes and which writes to the table is called from the form buttons. Somehow you must be calling the checkbox Listbox xdialog in your application, so you must have a way to call this UDF, too. There are no form events.

[den1s]

Re: Lookup values from a comma seperated field

Thanks Mike.

I will relook at this when i get a bit more time.

Denis

[den1s]

Re: Lookup values from a comma seperated field

Hi,

I am revisiting this function and have managed to get this to work, but only when viewing a report directly for the reports tab in alpha.

I have placed this into the report calculated field;

TRANS = com_strg_lookup("[PathAlias.ADB_path]\tbl_diciplines","full_pj_desc1",Project_Ids)

I have created this function (one is remmed out, i have tried both);

FUNCTION com_strg_lookup AS C (tablename AS C, fieldname AS C, strname AS C )
'	com_strg_lookup = crlf_to_comma(*for_each(foo,lookup(tablename,"dicipline_id = "+quote(foo),"alltrim("+fieldname+")"),comma_to_crlf(strname)))
	com_strg_lookup = left(strtran(*for_each(foo,lookup(tablename,"dicipline_id = "+quote(foo),"alltrim("+fieldname+")"),strtran(strname,",",crlf())),crlf(),","),-1)
END FUNCTION

Does anyone know why when running this directly from the browser, my function or calculated field is not finding any items?

Denis