Audit history use NULL as split symbol

Hi @larryly Try using the IsNull() function on the Generic Inquiry.

@ChandrasekharM It’s not working. The field is combined with Null. Not whole field is Null. I tried to use IsNull and substring. The system throw a  “The multi-part identifier "AUAuditValues.ModifiedFields" could not be bound” error. 

Hi @larryly If the "AUAuditValues.ModifiedFields" has “Null” included in the text the please try using the below formula where null is added within single quotes(‘)

Replace([AUAuditValues.ModifiedFields],’null’,’’) 

Thanks

@ChandrasekharM I also tried this way. It also not working. Either null or Null.

@larryly I know that you mentioned that you don’t have database access so this probably won’t help you, but I was able to do the following with AugSQL (click here) and wanted to share.

Now, I know that there could be pipes in the data so this isn't perfect, but the following delimits the fields with pipes. You could use something other than pipes or maybe use multiple characters like two pipes together.

I don't understand why I had to use "COLLATE Latin1_General_BIN", but I found that in a Google search somewhere and it seemed to work so I used it.

SELECT TOP 100 ModifiedFields,
REPLACE(ModifiedFields COLLATE Latin1_General_BIN,NCHAR(0) COLLATE Latin1_General_BIN,'|') 'ModifiedFieldsWithPipe'
FROM AuditHistory
WHERE CompanyID=2

I'm using this technique to create a SQL View that is the AuditHistory table, but with the new version of my ModifiedFields column that has pipes for the delimiter. Then I'll be able to work with it in Generic Inquiries.

CREATE VIEW dbo.MyViewsAuditHistoryWithPipes AS
SELECT CompanyID,BatchID,ChangeID,ScreenID,UserID,ChangeDate,
Operation,TableName,CombinedKey,
REPLACE(ModifiedFields COLLATE Latin1_General_BIN,NCHAR(0) COLLATE Latin1_General_BIN,'|') 'ModifiedFields'
FROM AuditHistory