How do you copy an Integer field to a UD String field in the same table?

shobdy · October 10, 2017, 9:22pm

Hello,

I am trying to copy PORel.PONum (Integer field) to PORel_UD.PONum_String_c (String UD Field).
I am really trying to get it to copy when the POHeader.PONum is entered in Purchase Order Entry Summary tab.

I have got it to work when an update happens to the PORel table but that is last resort.
Anybody have any ideas on how to get it to copy on load?

Thanks,
Shawn

Chris_Conn · October 10, 2017, 9:34pm

string POasString = PORel.PONum.ToString()?

I can’t see any useful reasons to make a UD field to store that though

shobdy · October 10, 2017, 9:43pm

I am trying to link My PORel table to TranGLC table for the GLAccount field. Unfortunately, the TranGLC fields are Strings, not Integers , so I need to copy the field to UD string to connect them.

If you know a better way to do it, I will be eternally grateful.

shobdy · October 10, 2017, 9:44pm

Where would I use the code? GetByID?

knash · October 10, 2017, 10:15pm

Where are you trying to do the join?

BAQ it should just work.

if it is bpm This is from the String to Int way. which might be the opposite of what you are trying to do.

int intPORel = 0;
int intPO = 0;
int intPOLine = 0;

foreach(var RelTranGLC in ttPORelTGLC.Where(ThisTranGLC=> (ThisTranGLC.GLAccount == "999000|01") && (ThisTranGLC.Updated() || ThisTranGLC.Added())))
	{
		int.TryParse(RelTranGLC.Key1,out intPO); 
		int.TryParse(RelTranGLC.Key2,out intPOLine); 
		int.TryParse(RelTranGLC.Key3,out intPORel); 
		var MyPORel = Db.PORel.Where(ThisPORel=>(ThisPORel.TranType == "PUR-UKN" 
											&& ThisPORel.PoNum == intPO
											&& ThisPORel.POLine == intPOLine
											&& ThisPORel.PORelNum == intPORel)).FirstOrDefault();
		if(MyPORel != null)
		{
			
			throw new Ice.Common.BusinessObjectException(
			new Ice.Common.BusinessObjectMessage("PO Cannot be GL Account 999000; Please update.")
			{
				Type = Ice.Common.BusinessObjectMessageType.Error,
			});
		}
			
	}

Chris_Conn · October 10, 2017, 10:26pm

I know jacksquat about GL but it seems like you could easily do it BAQ with subquery.

On subquery, you’d get your int value and cast it to string with a calculated field.
Then bring in the subquery and use the string field to compare. @knash is the BAQ guy, I bet you can butter him up and he’ll drop screenshots

Simple example of using calculated field to cast an int (PoNum) to a string. I’d filter that to get only the needed record.

knash · October 10, 2017, 10:39pm

Don’t be so crazy Chris. You can just join the fields. Epicor Magic takes care of the rest. Different Table but same concept. The VendorNum, Invoice Line, and InvExpSeq are all INTS.

Chris_Conn · October 10, 2017, 10:40pm

In that case - I reclaim this post in the name of science. Who knew Epicor was so fancy

knash · October 10, 2017, 10:42pm

lol I saw the post and was laughing. I should have taken a screen shot.

Chris_Conn · October 10, 2017, 10:54pm

I always find a solution. Just sometimes not the right one

shobdy · October 11, 2017, 1:16pm

Hey @knash,

I am looking for a BPM to copy the INT fields PONum, POLine, and PORelNum in Table PORel to 3 UD fields in the same table that are set to string.

I am doing it this way because I am trying to join to the table TranGLC which has those fields as strings.

The reason I am doing this way is because I need to show the GLAccount field on the report when you print a Purchase Order.

I wouldn’t think a BAQ would help me on that.

I thought this was easier than trying to copy the GLAccount field to a UD in PORel. Maybe I was wrong, I am not very well versed in BPMs yet, though I am learning more every day.

If you can find it in your heart to help me write the custom code to do tis, I would be forever in your debt .

I appreciate the help you and @Chris_Conn have been giving me.

Thanks,
Shawn

knash · October 11, 2017, 2:03pm

If you are trying to do this in a report. Then you might not need to copy the value to a custom field.

Are you on SQL Server? Do you know the name of your production database.

You can direct connect to the TranGLC from your report expression. Something like this. The TestE10 is the database not the reporting database.

This adds overhead when moving the report around from test to prod, unless you have the same name for the database.

=“SELECT T5.FieldName
FROM ShipHead_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
LEFT OUTER JOIN TestE10.dbo.CustXPrt T3 ON T2.Company = T3.Company AND T2.PartNum = T3.PartNum and T2.CustNum = T3.CustNum
"

From the other post.

Select *
from Erp.TranGLC glc
inner join PORel po on glc.Key1 = cast(po.PONum as varchar)
and glc.Key2 = cast(po.POLine as varchar)
and glc.Key3 = cast(po.PORelNum as varchar)
and glc.RelatedToFile = ‘PORel’

shobdy · October 12, 2017, 8:37pm

Hey @knash,

I tried what you said about accessing directly from SSRS and couldn’t get it to work.
I am pasting the part I created below. When I run it, it tells me the subreport could not be shown.


LEFT OUTER JOIN EpicTest10.Erp.TranGLC T5
  ON T3.Company = T5.Company AND T3.PONum = CInt(T5.Key1) AND T3.POLine = CInt(T5.Key2) AND T3.PORelNum = CInt(T5.key3) AND T5.RelatedToFile = 'PORel'"

I tried both EpicTest10.Erp.TranGLC T5 and EpicTest10.dbo.TranGLC T5
EpicTest10 is the database name.

Noticed I tried to convert the TranGLC key to Integers, is that not right?

Neither worked…
Anybody have any ideas?

Thanks,
Shawn

knash · October 12, 2017, 8:53pm

You need to cast your variables.

LEFT OUTER JOIN EpicTest10.Erp.TranGLC T5 ON T3.Company = T5.Company AND T3.PONum = CAST(T5.Key1 as int) AND T3.POLine = CAST(T5.Key2 as int) AND T3.PORelNum = Cast(T5.key3 as int) AND T5.RelatedToFile = ‘PORel’"

knash · October 12, 2017, 8:54pm

plus post the rest of your =select

There might be other things that might be needed to be tweaked

shobdy · October 12, 2017, 9:00pm

Here is the complete expression for the dataset ‘POHeader’ in the Purchase Order Report POForm_PORelPart.rdl

Notice I added your code to it to cast as Int, didn’t help.

Still getting the ‘Error: Subreport could not be shown’.


="SELECT T3.TranType as TranType_MPD,T1.CommentText,T1.Company,T1.FOB,T1.FreightPP,T1.OrderDate,T1.PONum,T1.PrintAs,T1.ShipToConName,T1.ShipViaCode,T1.Calc_AccountRef,T1.Calc_BillToAddrList,T1.Calc_Buyer,T1.Calc_CurDesc,T1.Calc_CurSymb,CAST( T1.Calc_ExistingDropShipReleases as nvarchar ) as Calc_ExistingDropShipReleases,CAST( T1.Calc_MultiShiptoAddresses as nvarchar ) as Calc_MultiShiptoAddresses,T1.Calc_PurTerms,T1.Calc_ShipToAddrList,T1.Calc_PlantAddressInRelease, T2.CommentText as PODetail_CommentText,T2.Company as PODetail_Company,T2.DocUnitCost,T2.MfgPartNum,T2.MfgPartOpts,T2.PartNum,T2.POLine,T2.PONUM as PODetail_PONum,T2.PUM,T2.RevisionNum,T2.SubPartNum,T2.SubPartOpts,T2.SubPartType,T2.Taxable,T2.VendorPartOpts,T2.VenPartNum,T2.Calc_CostPer,T2.Calc_DtLineDesc,T2.Calc_ExtCost, T3.AssemblySeq,CAST( T3.DropShip as nvarchar ) as DropShip,T3.DueDate,T3.JobNum,T3.JobSeq,T3.JobSeqType,CAST( T3.OpenRelease as nvarchar ) as OpenRelease,T3.POLine as PORel_POLine,T3.RelQty,T3.Calc_ShiptoAddrList as PORel_Calc_ShipToAddrList,T3.Calc_ShipToConName,
 T4.RptLiteralsLAsm,T4.RptLiteralsLDueDt,T4.RptLiteralsLExtPrice,T4.RptLiteralsLLine,T4.RptLiteralsLLineChargeSubtotal,T4.RptLiteralsLMfgPartNumber,T4.RptLiteralsLMslshS,T4.RptLiteralsLOrderDate,T4.RptLiteralsLOrderQty,T4.RptLiteralsLOurPartNumber,T4.RptLiteralsLPg,T4.RptLiteralsLPhone,T4.RptLiteralsLPONum,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLShRelReq,T4.RptLiteralsLClosed,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.RptLiteralsLJobNum,T4.RptLiteralsLSeq
FROM POHeader_" + Parameters!TableGuid.Value + " T1

LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2

ON T1.Company = T2.Company AND T1.PONum = T2.PONUM

LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " T3

ON T2.Company = T3.Company AND T2.PONUM = T3.PONum AND T2.POLine = T3.POLine

LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4

ON T1.RptLanguageID = T4.RptLanguageID

LEFT OUTER JOIN EpicTest10.dbo.TranGLC T5

ON T3.Company = T5.Company AND T3.PONum = CAST(T5.Key1 as int) AND T3.POLine = CAST(T5.Key2 as int) AND T3.PORelNum = Cast(T5.key3 as int) AND T5.RelatedToFile = ‘PORel’"

knash · October 12, 2017, 9:13pm

EpicTest10 is your database? Then try EpicTest10.Erp.TranGLC

shobdy · October 12, 2017, 9:21pm

I fixed the name and now I am getting this


Severity	Code	Description	Project	File	Line	Suppression State
Warning		[rsErrorExecutingSubreport] An error occurred while executing the subreport 'Subreport7' (Instance: 355iT0R0R0x0S3): Data retrieval failed for the subreport, 'Subreport7', located at: /POForm_PORelPart. Please check the log files for more information. 
  [rsNone] Data retrieval failed for the subreport, 'Subreport7', located at: /POForm_PORelPart. Please check the log files for more information. 
  [rsNone] Data retrieval failed for the subreport, 'Subreport7', located at: /POForm_PORelPart. Please check the log files for more information.		Y:\CustomReports\EpicTest10\PurchaseOrderForm - copy from Production\POForm.rdl	0

Do you happen to know where the log file is?

knash · October 12, 2017, 9:24pm

removing the last line the report runs like normal? The line you just added T5

shobdy · October 12, 2017, 9:25pm

Yes, you remove the last LEFT OUTER JOIN to the end and report works fine.