Assignment problem with lookup table

[Former Member]

Hi All,

I have a problem when executing "Assignment" operation for the data in lookup table.

The assignment itself is a very simple operation - UPPER(fieldname). I can manually mofidy the field to uppercase successfully.

There is no data in related main table.

The assigned field is a very normal field - not a keyword, not a DF, not a UF.

"Database Command execution error" comes when do assignment and I get detail error information from MDM log:

OLEDB error on PVGN50048584A/Businesspartner_m000 from statement: DELETE FROM A2i_49_CO_AddMembs WHERE CheckOut_Id=?

ErrorRecord->HResult/NativeErr: 0x80040e37/0

Description: Invalid object name 'A2i_49_CO_AddMembs'.

Source/File/Line: Microsoft OLE DB Provider for SQL Server/.\OLEDB_DBConnection.cpp/3331

can anyone help me to explain this problem?

Thanks in advance,

Bin

[Former Member]

No one has an idea?

[former_member192347]

You can not change value in Lookup field using Assignment.

In main table, based on a certain value in field F1 you can assign value to field F2 (type lookup).

You can do it like this:

IF(F1 = "DR", Partner Titles[Dr.], Partner Title[Record])

Note: "Dr." value is in Partner Titles Lookup table

and else part preserves the original value, if condition is false

I hope this is helpful.

Abhay

[Former Member]

Hi Abhay,

Thanks for your reply. I'm confused If value in lookup table can not be changed by assignment, why there is a assignments tab for lookup table and I can add assignment to it?

And a question to your example, what does "Partner Titles[Dr.]" mean?

I tried to change a lookup field value in main table, but failed. what I did is:

there is a lookup field "Country" in main table, and for one record it's value is "Algeria", when I execute assignment operation which makes it to be uppercase, the value "Algeria" disappeared.

Regards,

Bin

[former_member192347]

Bin,

What I mean is that, if you have a lookup table let's say countries, with values {USA, CANADA, MEXICO}. You can not add or change value in countries lookup table using Assignment.

What Assignment feature allows is, let's say if you have main table with field Country and that refers to countries lookup table,

there are two records in main table, 1st with value CANADA and 2nd with value MEXICO in Country field, then you change the value of Country field in Main table to USA using the Assignment.

You can not assign static string value to field on main table of type lookup. Use following syntax.

"Partner Titles[Dr.]" is syntax for assigning value from lookup table to field on Main table. The value "Dr." exists in "Partner Titles" lookup table.

If you have assignment on lookup type field on maintable and you want to retain the original value, if certain condition is TRUE, use syntax like, Partner Title.[Record], where "Partner Title" is field on main table of type lookup.

I hope that helps.

Abhay