StrataFrame Forum

Where does BOMapper store Nulltype and Null Replacment value?

http://forum.strataframe.net/Topic23998.aspx

By Charles R Hankey - 7/29/2009

I'd like to write some sprocs to handle stuff like setting the Nulltype and Null replacement values for UIDs , for example.



Can't find where they are stored in the strataframe data. Is there another table someplace that is joined to the fields table?



Guidance appreciated.



ER for BOMapper - default settings for data types - but just guidance on a tsql script to accomplish the same thing is sufficient and probably more efficient of framework developer time Smile
By Trent L. Taylor - 7/30/2009

Are you just wanting to make sure that your result sets come back without any NULL values? If so, then you will want to wrap the column with a COALESCE statement which will return the first value that is not NULL.



SELECT

COALESCE(CN.cn_NoteTitle, '')

FROM Customers AS CS

LEFT JOIN CustomerNotes AS CN ON CN.cn_cs_pk = CS.cs_pk

WHERE CS.cs_pk = 1234
By Charles R Hankey - 7/30/2009

Thanks Trent. I'll have to meditate on that one. Currently, I have a lot of data where pks and fks are UIDs. The fks (or more accurately the keys to lookup data) have to allow nulls as in many cases they are indeed null. I am currently setting it up in the BOMapper to return a blankguid() ( my bo class has a function that calls a shared method of my utility class )



Public Shared Function BlankGuid() As Guid

Return New Guid("00000000-0000-0000-0000-000000000000")

End Function




FWIW Most of my BOs are filled with Sprocs



I had forgotten about coalesce but I think I still need to handle nulls in the BOMapper



which goes back to the original question - just out of curiosity, where is that info stored when you set it in the BO Mapper. I can find nothing in the Strataframe database to show the Return Alternate on null and the null value of me.blankguid()



(it does work fine, just takes longer to set up than I would like and I was thinking rather than ask for a ER in BOmapper that would be justifiably far down on the priority tree in the interim if I understood what the BOMapper was doing to the metadata I could do mass changes with T-SQL - after backing everything up at least twice BigGrin )



Think Jerry will sign Vick for the Boys ? Tongue
By Trent L. Taylor - 8/3/2009

So what are you wanting the BO Mapper to do then? Are you just trying to return a blank GUID when the underlying data value is NULL? As you are probably already doing, you can do this is a custom property override. But I want to make sure I am not missing something.



Also, you could just return an alternate value as well and just put in your utility function as the return value.



Think Jerry will sign Vick for the Boys ?




Well, has has the right resume for one of his projects....but I am most definitely pulling for NO! Hoping to have a delinquent free year! Smile
By Greg McGuffey - 8/3/2009

I think Charles is wanting to write a sproc that would setup a bunch of columns to use the alternate null settings directly. I.e. rather than go into the BO Mapper and setup 100s of columns manually, just run a sproc to set them all at once by directly manipulating the meta data that SF stores in the db. Then he'd open the BO mapper, rebuild his partials.



So, if I'm correct, he's wanting to know were this meta data is stored within the SF database. Is that right Charles?
By Trent L. Taylor - 8/3/2009

If that is the case, and I caution you on this as it can break a lot of stuff, but you will want to use the DTEProjects tables. Specifically you are wanting to create exceptions. Is this not something you can do through the Customization Wizard?
By Charles R Hankey - 8/3/2009

This can definitely be done in the Bomapper and the wizard helps to a certain extent but as Greg will testify the process is a bit awkward, especially when you hav 100 tables and the data exceptions are pretty standard ( empty dates are nulls, keys are all uniqueidentifiers which must be null on the backend when they really are unknown, that kind of stuff)



The wizard helps but it is cumbersome in this case and I wouldn't ask for an ER for this use case as I don't think the need is broad enough and there are other things more important.



I think it would be nice to have default for handling nulls that could be set up based on data type and greg's previous suggestion of being able to flag projects or specific BOs as irrelevant to the BO mapper and thus have them excluded from the Tree, or at least from trying to make partials.



But I'll play with a sproc ( caveats noted, strataframe will be backed up frequently while figuring it out Smile )