MDM Data Manager Assignments using Dates

[Former Member]

Hello.

I am trying to create some assignments that default to a certain date and my expressions are getting the error message when I execute them.

I am currently trying to create an Assignment for the Look-up table: Valid To (which belongs to the QT: Sales Data).

I want the Valid To Assignment to default at the date 12/31/2999 if the user did not already enter a value.

My expression looks like this:

IF (IS_NULL(Sales Data.Valid To), SYSTIME(0) = 12/31/2999)

I linked Sales Data for my 'Table Field'

Any Ideas?

Points awarded promptly for any help!

Thanks,

Nichole

[Former Member]

Nichole,

You're almost there...

IF(IS_NULL(Sales Data.Valid To), 2999:12:31)

Table Field = Sales Data.Valid To

You'll want to pick the functions and field from the editor's menus as usual - and only type in the constant date by hand.

...Dee

[Former Member]

Dee,

Still getting the error...

Under my table field drop down they do not give me the option of Sales Data:Valid to. There is just a 'Sales Data.'

Could this be the issue?

Thanks so much for your help!

[Former Member]

Hi Nichole ,

Under my table field drop down they do not give me the option of Sales Data:Valid to. There is just a 'Sales Data.'

- if the Valid to Field is a Qualifier then this may not work.

As the qualifier field value gets populated by the link that we create between the main table record and the qualifier table ffields while mapping.

Hope It Helped,

Thanks & Regards

Simona Pinto

[Former Member]

My Valid To field is actually a look up table. Sales Data is the Q.T.

Other assignments that i've made work with just the 'sales data' table field even though there is a lookup table within that Q.T.

Thanks for the help!

[Former Member]

Hi,

As per my knowledge, writing Assignments on Qualified Table field within the Main table is not possible for both Qualifier as well as Non-Qualifier Field. As if you want to write Assignment on Non-Qualifier fields only then you can do while selecting Qualified table as your Current Table not as Main table.

For Qualifier field too if you want to asign field using Main table is not Possible as you cant select Property Table Field as Valid to here you could only able to select all Main table fields and fields which is look up to this Qualified table and other Sub tables.

Don't confuse with other subtables.

Other assignments that i've made work with just the 'sales data' table field even though there is a lookup table within that Q.T.

That's why as you could get values for all other subtables in Assignment Expression as lookups tab if you remember like Reconcillation but here you could not find field who look up to your Qualified Table as well as Qualified table. there indirectly u r refering field to this lookup table and you get values here to assign but how could you assign value to Valid to field just think even if this is must requirement for you then you create one more subtable and look up this valid to field to this subtable or just create one more field Valid to in maintable of type text.

So from my point of view this is only drawback that we could not Assign Value to Qualified table field. I hope it will help you in understanding. But writing Validations for Qualified Table fields is Possible but not Assignments. May be in MDM 7.1 its possible. This is my knowledge about Writing Expressions for Qualified Table Fields till now.

Hope it will Help you,

Rewards if found Useful.....

Thanks,

Mandeep Saini

[Former Member]

Hi Mandeep.

Thank you for walking me through that.

I think I understand what you are saying about 'Valid To' not having a lookup value to 'link to' essentially in the expression editor.

But here is the strange part:

My assignment for Reconciliation Accounts STILL don't work. I have other assignments that are identical to the expression in Reconciliation Accounts that DO work, and I have assignments that use Qalified Tables as the 'Field Value' that work also. Just some of them don't work.

Here is an example:

My assignment for 'Individual Limit' which says the field must be < = 99999999999999 characters in length works. Individual Limit links to the Qualified Table 'Sales Data.'

My expression looks like this:

IF (IS_NULL(Sales Data.Individual Limit), LEN(Sales Data.Individual Limit) <= 99999999999999

I used "Sales Data" for the Table Field

My other assignment for Reconciliation Account which says if the field is null to create 155001 does NOT work and looks like this:

IF (IS_NULL(Company Code Data.Reconciliation Account.[Record]), VT_Reconciliation Account[155001])

I used "Sales Data" for the Table Field and this does NOT work.

So some work when using qualified tables and some do not...even though the expression structure is identical...

Hope that made sence!

[Former Member]

Hi,

I am not getting you fully but for your validation as you mentioned:

IF (IS_NULL(Sales Data.Individual Limit), LEN(Sales Data.Individual Limit) <= 99999999999999

it does not make sense as when field is null how could it check for the length first it shud be like this

IF (IS_NOT_NULL(Sales Data.Individual Limit), LEN(Sales Data.Individual Limit) <= 99999999999999)

Morover here len will not work it is different one

suppose if you say LEN(Field) < 3 it does not mean it shud be 1 and 2 it mean we can take Numeric values from 1 to 99 and Text value AB not ABC mean lenth upto 2 chars.

so above you r adressing totally different thing

Try this:

IF (IS_NOT_NULL(Sales Data.Individual Limit), LEN(Sales Data.Individual Limit) <= 14, FALSE)

Also, write False

ultimately it will not let you write value greater than 99999999999999

mean for this if your field contain value 99999999999999 it works and for 100000000000000 and above values it fails as well as for Null values which you want.

Rewards if found Useful......

Thanks,

Mandeep Saini

Edited by: Mandeep Saini on Aug 6, 2008 5:44 PM

[Former Member]

Hi,

I try to make you more clear in this, it works for

IF (IS_NULL(Company Code Data.Reconciliation Account.Record), VT_Reconciliation Account) because here Field in maintable is Company Code Data is look up to sub-table and i hope you are assigning field Company Code Data for Table Field, so writing a assignment for sub-table works if and only if a field for main table is selected as Table Field. I used "Sales Data" for the Table Field and this does NOT work. it shud not work for Sales Data as if you see in MDM Console in Main table your Sales Data field is not looking up to your Subtable which is look up for your Company Code Data field, even if both the fields are looking to same sub-table it does not make sense but if you are maintaing same table for both the fields then again your need change mean: IF (IS_NULL(Sales Data.Reconciliation Account.), VT_Reconciliation Account)

So both assignments are independent of each other and you have to write two Assignments as the fields in main tables exist differently.

Hope this time you will get better idea and hope in solving your Query,

Thanks,

Mandeep Saini

Edited by: Mandeep Saini on Aug 6, 2008 6:34 PM