I have a field that holds a series of department id’s that are separated by spaces. I want to set up a relation to this table using the “LIKE” operator. But all I keep getting is exact matches. Perhaps I don’t understand how the “LIKE” operator is supposed to work.
Any enlightenment?
Hi elyod_72,
“LIKE” operator in relation work just like “LIKE” operator in SQL, wildcard searching…
You would have to do something LIKE %valuepart%.
Like Ars said this is 1:1 with SQL.
BTW I don’t think this is what you need, it looks like you should use IN but with that you can not build a relation as far as I kow.
Why are you storing the id’s in one field?
Can’t you normalize that?
To be specific, to make a LIKE relationship work you need to make a stored calc field for the left side of the relationship that does something like this:
return "%" + id_field + "%"
Then match to your field of IDs on the right side and it will return what you expect.
I agree with Marcel that normalizing this kind of a structure is preferable most of the time. But there have been a few times where I have put this type of relationship to good use.
Why are you storing the id’s in one field?
Can’t you normalize that?
I agree, and for 99% of the time, this is not the SOP.
Let me break down my situation better. Because maybe there is a better way to do this that I am not thinking of.
I have a table of job tasks that we routinely do in the company. These tasks can be done by only one department, or by several. I have a column describing the task, and then a department_id column that will list of the department id’s that will use this task like this:
department, task
1, dustbust
1 2 3 4, paint
3 4 5 6, cleanup
I then have a bidding layout with a task child table that specifies what tasks are being bid out. I want to create a valuelist for the tasks bid based to the department type of the bid.
I assumed the way to do that was to relationally link the bid_type to the task list like this:
bid_task.bid_type LIKE task_list.dept_id
So, is there a better way to do this?
What about doing the valuelist drop-down in a method? If I do it there, how do I make the field see the valuelist?[/u]
I am not too clear in my head right now ( I really think I am going to take a couple of days off with Christmas and Newyear ).
But wouldn’t a join table do the job here for you?
Hi,
I think the way to do this relationship as you want, if I understand the problem correctly, is as follows (although I too think like Marcel that you’d be better off having a related table to the ‘task’ table containing a task_id and a dept_id on each row - dead easy then to create a valuelist of available departments related to the specific task):
But doing it the other way… You have a dept_id column where there is one id (such as ‘1’ or ‘5’ or whatever) and you want to see if the chosen department is able to do a particular task. All the departments that are able to perform this task are listed in the ‘task_list.dept_id’ column separated by spaces like this ‘1 3 7’ and you want to see if the selected department id is within that list. Do it like David said but I’d change it slightly like this:
return "% " + id_field + " %"
```(notice the space on each side of the id_field)
AND I'd make sure when filling the 'task_list.dept_id' column with dept_ids that there is a space on BOTH sides of each dept_id that is added to the task. (This makes sure that the first as well as the last id in the column has at least one space on each side of it; the way it is now there is no space before the first one nor after the last one). The problem with a 'LIKE' and wildcards ('%') without the separating spaces on each side of the ids is that otherwise there is no way to distinguish say a '1' and a '12' as a '%1%' will find them both. The alternative is to either be sure that there are never more than 10 different ids (0-9) or, if more than 10, that preceding zeros make each 'number id' unique (001,002 and so on for example if less than 1000). Does that work for you?I will give it a try.