Hi Community,
I have Two Tables, they are - “stagePurchTable, stagePurchLine”
Note : “stagePurchTable” is Header Table & “stagePurchLine” is Lines Table.
There is a field Named “ErrorMessage” in “dMFStagingValidationLog” Table.
The Relation between these Table are -
dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId;
dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId;
My Requirement is :
If a data file is Imported and it have Error on both Headers and Lines, Then those Errors of two tables should Concatenate into one single field “ErrorMessage”.
-Thanks in Advance
All right, so do you have question or what do you need from us?
In short, you need to decide whether you really have to duplicate the information as another place, instead of concatenating the values when they’re needed (e.g. before displaying to users). If you have to, you need to decide where you’ll store the information and how you’ll combine the value (e.g. you’ll want to identify the line that the messages are related to). Then you’ll fetch the original messages, concatenate them (you can use + operator, strFmt() or so), assign the value to a field and save the record.
Here is the Code I used on DP class for SSRS report:
public void processReport()
{
super();
utcdatetime fromDateTime;
utcdatetime toDateTime;
contract = this.parmDataContract() as MC_StagePurchErrorContract;
_fromDate = contract.parmFromDate();
_toDate = contract.parmToDate();
fromDateTime = DateTimeUtil::newDateTime(_fromDate, 0*0*0, DateTimeUtil::getUserPreferredTimeZone());
toDateTime = DateTimeUtil::newDateTime(_toDate, 24*60*60, DateTimeUtil::getUserPreferredTimeZone());
while select crosscompany * from dMFDefinitionGroup
join dMFExecution where dMFExecution.DefinitionGroup == dMFDefinitionGroup.DefinitionGroupName
join stagePurchTable where stagePurchTable.ExecutionId == dMFExecution.ExecutionId
&& dMFExecution.CreatedDateTime >= fromDateTime
&& dMFExecution.CreatedDateTime <= toDateTime
&& stagePurchTable.TransferStatus != DMFTransferStatus::Completed
&& dMFDefinitionGroup.ProjectCategory == DMFProjectCategory::Integration
&& dMFDefinitionGroup.OperationType == DMFOperationType::Import
{
select * from dMFStagingValidationLog where dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId|| dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId;
select * from stagePurchLine where stagePurchLine.ExecutionId == dMFExecution.ExecutionId;
stagePurchTableTmp.clear();
stagePurchTableTmp.DefinitionGroup = dMFExecution.DefinitionGroup;
stagePurchTableTmp.Description = dMFExecution.Description;
stagePurchTableTmp.Company = DMFFormUtil::executionCompany(dmfExecution.ExecutionId);
stagePurchTableTmp.ExecutionId = stagePurchTable.ExecutionId;
stagePurchTableTmp.TransferStatus = stagePurchTable.TransferStatus;
stagePurchTableTmp.ErrorMessage = this.getErrorMessage();
stagePurchTableTmp.ExecutionTime = dMFExecution.CreatedDateTime;
stagePurchTableTmp.OperationType = dMFDefinitionGroup.OperationType;
stagePurchTableTmp.ProjectCategory = dMFDefinitionGroup.ProjectCategory;
stagePurchTableTmp.DocumentType = stagePurchTable.DocumentType;
stagePurchTableTmp.AccountNum = stagePurchTable.AccountNum;
stagePurchTableTmp.insert();
}
}
public str getErrorMessage()
{
if(dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId && dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId)
{
FinalErrorMgs = dMFStagingValidationLog.ErrorMessage + ", " +dMFStagingValidationLog.ErrorMessage;
}
else if(dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId || dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId)
{
FinalErrorMgs = dMFStagingValidationLog.ErrorMessage;
}
return FinalErrorMgs;
}
Here rather than using another method “getErrorMessage” for Concatenate, is there any possible way to use here “stagePurchTableTmp.ErrorMessage = this.getErrorMessage();”
Note: For the Above “getErrorMessage” method, I did used is wrong and I am not getting “ErrorMessage” Data from the Lines Table, But the Data of Header Table is Repeating in the place of Lines Table Data
I did Changed the code into:
For the relation having - “dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId || dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId;”
str FinalErrorMgs;
FinalErrorMgs = dMFStagingValidationLog.ErrorMessage + ", " +dMFStagingValidationLog.ErrorMessage);
stageSalesTableTmp.ErrorMessage = stageSalesTableTmp.ErrorMessage + FinalErrorMgs;
But still The Error Message data of Header Table is repeating in Lines Table.
I see several quite a few bugs in your code.
For example, you’re ignoring fields like ExecutionId and EntityName on DMFStagingValidationLog, therefore you may be getting logs for a completely unrelated tables and unrelated exports or imports.
The following condition gets met only if the records in stagePurchTable and stagePurchLine have the same RecId, which may happen only by accident and it has nothing to do with your actual requirement.
dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId
&& dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId
Here you’re using the same value (dMFStagingValidationLog.ErrorMessage) twice.
dMFStagingValidationLog.ErrorMessage
+ ", "
+ dMFStagingValidationLog.ErrorMessage
Please fix these bugs and debug your code. If it you still see problem that you can’t resolve, show us your new code, explain the problem and tell us what you’ve found when you tried to debug it.
Okay and For the above code I did added all these relations -
while select * from dMFDefinitionGroup
join dMFExecution where dMFExecution.DefinitionGroup == dMFDefinitionGroup.DefinitionGroupName
join stagePurchTable where stagePurchTable.ExecutionId == dMFExecution.ExecutionId
&& dMFExecution.CreatedDateTime >= fromDateTime
&& dMFExecution.CreatedDateTime <= toDateTime
&& stagePurchTable.TransferStatus != DMFTransferStatus::Completed
&& dMFDefinitionGroup.ProjectCategory == DMFProjectCategory::Integration
&& dMFDefinitionGroup.OperationType == DMFOperationType::Import
{
select * from dMFStagingValidationLog where dMFStagingValidationLog.StagingRecId == stagePurchTable.RecId|| dMFStagingValidationLog.StagingRecId == stagePurchLine.RecId;
select * from stagePurchLine where stagePurchLine.ExecutionId == dMFExecution.ExecutionId;
----- Remaining code
}
And I did declared Str FinalErrorMgs at the beginning of the method. And I wrote -
FinalErrorMgs = dMFStagingValidationLog.ErrorMessage + ", " +dMFStagingValidationLog.ErrorMessage);
stageSalesTableTmp.ErrorMessage = stageSalesTableTmp.ErrorMessage + FinalErrorMgs;
And as you mentioned above that for " dMFStagingValidationLog.ErrorMessage+ ", "+ dMFStagingValidationLog.ErrorMessage, I did used the same value twice.
So please can you suggest be the better way to do that, I am not able to understand the using of concatenating function to this requirement.
The first step is deciding what output you want to get. Then you need to decide where to store it, if at all.
If the File have error in both Header and Lines, then In report It need to show as [Header’s Error message+ “,” + Line’s Error message] in the Same Row. And the Data need to store in Temporary Table while running the report.
That’s not sufficient. For example, the temporary table may have more than a single record; you need to define which record(s) you want to use.