ILF() function

[Arlmachado]

I want to create a general ILF function for a columm.

As shown on the image attached, I have two empty columms "Codes" and "33/SDGL". I want to create a function (will have to be a big expression I know) that connects both columns to the "Bindungen" column.

As an example of the expression I am looking for, for the columm "33/SDGL", is that when the bindungen in field 490 has 33 and SDGL, that on the respective field of the "33/SDGL" columm it appeas "Both". But if it only has 33, like field 480, then it appears just 33. And if it has neither then blank.

My other expression for the columm "Codes" relates to the code number it appears on the "Bindungen" columm. Like in field 480, it states 33,812B - I would like the 812B to appear in the "Codes" columm.

I would appreciate any tips of how I would go about it.

 

[Ranman256]

Is this an excel sheet?

 

[plog]

I didn't exactly follow what you are trying to do, but I did look at your image, and that table isn't properly set up. The data in there needs to be in at least 2 tables--not jammed into 1.

1. Discrete pieces of data need to be stored discretely. You shouldn't have a field that has multiple values seperated by commas. That means your Bindungen field should not exist.

2. You shouldn't store values in table/field names. I believe BBG & 33/SSDGL (you also shouldn't name fields with non-alphanumeric characters) shouldn't be fields in a table, but values in a field.

3. When you start numerating field names, that data should go into a whole new table. All the data in those BC_ fields need to be put into their own table and related back to the one you posted. The structure of that table would look like this:

tblBC
BC_ID, autonumber, primary key of table
ParentID, number, foreign key back to the table you currently have--will hold the ID value there
BC_Num, number, will hold the numeric part of the current BC name
BC_Value, text, will hold the value currently in the table you posted (e.g. 33, 812B, etc.)

My guess is that fixing your structure issue will make this issue either go away or be solved with a simple query.

 

[sneuberg]

As an example of the expression I am looking for, for the columm "33/SDGL", is that when the bindungen in field 490 has 33 and SDGL, that on the respective field of the "33/SDGL" columm it appeas "Both". But if it only has 33, like field 480, then it appears just 33. And if it has neither then blank.

What if it has SDGL but not 33? Should it be blank or SDGL?

My other expression for the columm "Codes" relates to the code number it appears on the "Bindungen" columm. Like in field 480, it states 33,812B - I would like the 812B to appear in the "Codes" columm.

What's the rule? Is it for example, If 33 then whatever follows it otherwise null?

And now for the important question. Is Bindingen a multivalued field, just a plain text field that happens to have comma separated values, or formed from the fields BC_1, BC_2, ...?

 

[Arlmachado]

What if it has SDGL but not 33? Should it be blank or SDGL?

Then its SDGL.

And now for the important question. Is Bindingen a multivalued field, just a plain text field that happens to have comma separated values, or formed from the fields BC_1, BC_2, ...?

I believe it is formed by BC_1, etc. I didn´t make the table originally. I just receive it and need to work with it.

 

[sneuberg]

I believe it is formed by BC_1, etc. I didn´t make the table originally. I just receive it and need to work with it.

Then I agree with plog and suggest you ensure the structure is correct before proceeding with this. I suggest checking out this series of pages on normalization.

If there are a lot of records and you need some code to move the data from the columns from the fields BC_1, BC_2, ...etc to another table let me know. But if that turns out to be the the case then please upload your database or a stripped down version with at least this table so I have something to test the code against.