Keep UD table synced like extended UDfields

[Banderson (Brandon Anderson)]

So I have kind of an odd solution that I have working right now that I would like to improve on.

If you feel like a really long read here’s the post where I figure out how to get what I need on the updating UD08

Unions and performance

Ideally, my solution would work like this. Add and extended UDField onto JobAssembly and JobMtl table, in which there was a PackStatus which is a short string to indicate when the part was assembled, shipped lose, or a couple of other possible statuses.

The reason that doesn’t work it because the JobEntryBO does not work efficiently enough to be able to update these statuses in any sort of sustainable way. So, to get around that I populated the UD08 table with the keys to match up the job tables. Updating UD tables is quick because there are no special rules.

That part works fine. What I want to improve on is my synchronization method. Currently, I have a dashboard that looks for things that need be added, and we feed that into UD08 using DMT. It works ok, but I want to see if I can get the system to sync itself automatically.

I have two trains of though right now. One is to use BPM’s to try and do this. I try to keep to the widgets in the BPM’s as I don’t have the coding chops to do the custom code (yet) Unfortunately, I can’t use data directives because they don’t allow calling other BO’s. So I’m stuck with method directives, but there are a lot of ways to change a job and they call different methods, so I can’t just use update, it would have to find them all.

My second train of thought is to revisit the idea of the extended UD field, but then instead of using the JobEntry BO, just code directly to the database. (it just means I have to rebuild all of the other parts that are already working on UD08) I would still have to custom code, but it makes other things much simpler.

Has anyone else ever done anything similar to this? Any better ideas?

[josecgomez]

I don’t fully follow. You want to update all the jobmtls and or jobassy records with a single status across the board?

[Banderson (Brandon Anderson)]

no, I have a dashboard where every assembly or material needs to have a pack status assigned to it. Someone is looking at a print and says “everything is assembled” so has mass selects and sets all of the parts to assembled. Or “everything packed loose” or "some is assembled and some packed, etc… They need to be able to quickly go through a list and set the appropriate status.

[hkeric.wci (Haso Keric)]

I was able to use a Custom Action with a BPM Form, then I did it via the Updatable BAQ BPM. In addition the Dashboard Published fields for visibility via bpmContext. May be a route to take.

[Banderson (Brandon Anderson)]

so you are saying I could use this to populate my UD08 table? (create, delete, and update rows as appropriate?)

[Hally (Simon Hall)]

I’m ready to get slammed here…but a dirty method would be to have an Update trigger on the UD_JobAssembly and UD_JobMtl and add/change data in the UD table. I DON’T RECOMMEND THIS, but you were asking for suggestions

I am thinking you could use a scheduled service connect workflow that runs a dbOperation that replicates the baq. and then iterates through the result set with a subprocess and then do your UD08 update. Of course that is if you have ESC.

Probably other ways, but just off the top of my head.

[Banderson (Brandon Anderson)]

@Hally

but a dirty method would be to have an Update trigger on the UD_JobAssembly and UD_JobMtl and add/change data in the UD table.

That’s what I started trying to do, but quickly realized that changes can be made that don’t hit the update method on JobEntry, so there are holes.

@Hally

I am thinking you could use a scheduled service connect workflow that runs a dbOperation that replicates the baq.

We have service connect, but I don’t know how to use it. But that is an idea that I should maybe explore even if it’s just for the sake of learning the tool.

[Hally (Simon Hall)]

@Banderson

That’s what I started trying to do, but quickly realized that changes can be made that don’t hit the update method on JobEntry, so there are holes

I was talking a SQL trigger. That monitors when the data in the table changes. As I said, forget that method.

[hkeric.wci (Haso Keric)]

Maybe… Just remember you can add “BPMs” On Update, Custom Action etc on the BAQ itself and do basic LINQ to Update any table, especially custom columns.

// Assign CCDtl.CDRCode
//
// 10/30/2018 hkeric: Initial Implementation
//

Action<string> log = message => {
	Ice.Diagnostics.Log.WriteEntry(message);
};

string companyID = callContextBpmData.ShortChar01;
string plantID = callContextBpmData.ShortChar02;
string whseID = callContextBpmData.ShortChar03;
int ccYear = (int)callContextBpmData.Number01;
int ccMonth = (int)callContextBpmData.Number02;
int ccSeq = (int)callContextBpmData.Number03;
bool ignoreSeq = callContextBpmData.Checkbox01;
string selectedReasonCode = callContextBpmData.Character05;

log("companyID: " + companyID);
log("plantID: " + plantID);
log("whseID: " + whseID);
log("ccYear: " + ccYear.ToString());
log("ccMonth: " + ccMonth.ToString());
log("selectedReasonCode: " + selectedReasonCode);


//
// Update the ttResults Rows Frontend User sees
//
var query = (from r in queryResultDataset.Results
	where
		r.CCHdr_Company == companyID
		&& r.CCHdr_Plant == plantID
		&& r.CCHdr_WarehouseCode == whseID
		&& r.CCHdr_CCYear == ccYear
		&& r.CCHdr_CCMonth == ccMonth
		&& r.Calculated_CDRMandatory == true
		&& r.Calculated_EnableCDRCode == true
	select r);

// In BPMForm you can select to ignore the Sequence
if (ignoreSeq == false)
{
	log("ignoreSeq: false (Applying ccSeq to ttResults)");
	log("ccSeq: " + ccSeq.ToString());
	query = query.Where(r => r.CCHdr_CycleSeq == ccSeq).ToList();
}

log("ttResults Matches: " + query.Count());

// Assign Selection to the Dashboard (Frontend)
foreach (var row in query)
{
	row.CCDtl_CDRCode = selectedReasonCode;
	row.SetRowState(IceRowState.Updated); // Not Needed
	//row.RowMod = "U";
}


if (query.Count() > 0)
{
	//
	// Update Actual CCDtl Record
	//

	// Get CCDtl Rows
	var query2 =
		(from ccd in Db.CCDtl.With(LockHint.UpdLock)

			where
				ccd.Company == companyID
				&& ccd.Plant == plantID
				&& ccd.WarehouseCode == whseID
				&& ccd.CCYear == ccYear
				&& ccd.CCMonth == ccMonth
				&& ccd.VarToleranceStat == 2 && ccd.PostStatus == 0
		select ccd).ToList();


	// Merge CCDtl with ttResults to Narrow down our Targets
	var query3 =
		(from ccd in query2

			join r in query
				on new {
					Company = ccd.Company,
					Plant = ccd.Plant,
					WarehouseCode = ccd.WarehouseCode,
					CCYear = ccd.CCYear,
					CCMonth = ccd.CCMonth,
					CycleSeq = ccd.CycleSeq,
					PartNum = ccd.PartNum
				} equals new {
					Company = r.CCHdr_Company,
					Plant = r.CCHdr_Plant,
					WarehouseCode = r.CCHdr_WarehouseCode,
					CCYear = r.CCHdr_CCYear,
					CCMonth = r.CCHdr_CCMonth,
					CycleSeq = r.CCHdr_CycleSeq,
					PartNum = r.CCDtl_PartNum
				}

			where r.Calculated_EnableCDRCode == true

		select ccd).ToList();


	log("Update CCDtl with CDRCode");
	log("CCDtl Targets: " + query3.Count());

	// Assign Selection to the CCDtl Record (Backend)
	foreach (var row in query3)
	{
		row.CDRCode = selectedReasonCode;
	}
}

Db.Validate();

It would fire then on your Dashboard and remain isolated within the UBAQ + Dashboard, for maintenance purposes.

[Banderson (Brandon Anderson)]

@Hally

I was talking a SQL trigger.

Ok, I’ll still probably look it up just so I know what it is!

@hkeric.wci, so this would basically just be a way to skip the DMT part then right? That’s at least a step in the right direction. Then I could maybe call a dynamicqueryadapter via button on job entry to sync it whenever we needed. That might be better anyways so I don’t tank performance where it’s not needed. Good idea. I’ll explore that tomorrow.

[Hally (Simon Hall)]

@Banderson

Ok, I’ll still probably look it up just so I know what it is!

Always for extending knowledge. Here’s some links.

Create SQL Server Trigger

http://www.sqlservertutorial.net/sql-server-triggers/

CREATE TRIGGER (Transact-SQL) - SQL Server

[josecgomez]

@Hally noooooooo

[Hally (Simon Hall)]

Yep Exactly…

[Mark_Wonsil]

And we know what happened to the cat when someone started to poke around in there…

(Dead)

[Banderson (Brandon Anderson)]

So just an update for everyone, I solved this by creating 3 queries. One for adding new records, one for deleting records, and one for updating records. In the customization on the dashboard (shown previosly) I added a button to trigger the dynamic queries, and then looped through the results calling the adapter for UD08 to add, delete or update respectively. After some fiddling around with adapters I was able to get everything to work. I was worried about performance to be slow, but it’s surprisingly quick, probably thanks to the UD BO not really having any special rules to follow.

I probably (maybe should have??) done all of this on the server side with a BPM on my queries, but I’m a little more familiar with the client side, so that’s the way I went.

At the end of the day, the guy that’s doing the work can hit one button when he needs the tables updated, it takes about 5 to 10 seconds to run if there is a lot to update, and he’s ready to go. Much better than us manufacturing engineers copy pasting into DMT anymore.

Thanks for the discussion.

[hkeric.wci (Haso Keric)]

@josecgomez uses this technique often where he makes a UBAQ do logic and invokes it in a BPM or Customization. This lets him share this logic, across multiple required BPMs / Customizations… Like a Shared Function if you will. Even if he needed a DMT to invoke it, he will simply call the UBAQ in the lets say OrderHed.Update He also has methods to pass variables to the UBAQ. But that’s a story for another day.

[A.Baeisa (Al)]

great work @Banderson,
is it possible to share your dashboard customization script editor here ?

[Banderson (Brandon Anderson)]

It wouldn’t be relevant to anyone else but our business. We have custom built solutions, UD fields, and relatively unique business model, so it isn’t going to work for anyone else. I didn’t use anything that you can’t find using the wizards or the the well circulated video of how to call a dynamic query.

[A.Baeisa (Al)]

no problem mate