Notification for Duplicate PartNum on BOM - Method Directive

Kinetic ERP
, ,
1

Hi all,

I am working on creating a method directive that shows a pop-up message whenever a PartNum is added to a BOM that is already on there. It should still allow the user to add it, just notify them of this occurence.

The following was my crack at it:

image
image524Ă—494 16.1 KB

  1. Condition: There is at least oneaddedrow in theds.ECOMtltable
  2. Condition: dsECOMtlRow.PartNum == dsECORevRow.PartNum & dsECOMtlRow.RevisionNum == dsECORevRow.RevisionNum
  3. Set Variable: Set the [MtlPartNum] argument to the [dsECOMtlRow.MtlPartNum]
  4. Condition: TheMtlPartNumargument/variableis equal to the dsECOMtlRow.MtlPartNumexpression
  5. Show Message: “Part number is already listed in the BOM .”

It currently shows the message, but shows it regardless of whether the part is already on the BOM or not. I believe I need some way to directly reference the table of materials for the rev being used in the BOM.

Any help/guidance is greatly appreciated!

2

Here’s just a few things I noticed:

First, just for future reference, any table joins between ECO tables should include the GroupID and AltMethod. Once you have multiple groups working on the same part, it’ll cause headaches if a material was added in one group, removed, then added again.

Second, what happens when they update a material’s part number? No message will be displayed in this case since you’re only checking added rows. If that’s intended, then you can ignore this bit.

Third, what you’re essentially doing is comparing the same ECOMtl row with itself. When you set the MtlPartNum, the next condition is checking if the part number is equal to itself. What you’ll want to do is compare dsECOMtlRow against all other ECOMtl rows in the database where the MtlSeq key fields are not equal.

3

How would you go about comparing dsECOMtlRow against all other ECOMtl rows in the database where the MtlSeq key fields are not equal?

4

Easiest way is to put a custom code widget after Condition 0 when true. I grabbed some code from a similar BPM I have in place already and tweaked it a little for your use case.

Make sure to change Condition 0 by adding a new line, set it to “The specified field has changed from any to another”, and select the ds.ECOMtl.MtlPartNum field.

// Get the material row from the data set.
var ecoMtlRow = ds.ECOMtl
	.Where((r) => r.Added() || r.Updated())
	.FirstOrDefault();

if (ecoMtlRow != null)
{
	// Check database if material with same part exists.
	var dbExists = Db.ECOMtl
		.Where((r) =>
			r.Company == ecoMtlRow.Company
			&& r.GroupID == ecoMtlRow.GroupID
			&& r.PartNum == ecoMtlRow.PartNum
			&& r.RevisionNum == ecoMtlRow.RevisionNum
			&& r.AltMethod == ecoMtlRow.AltMethod
			&& r.MtlPartNum == ecoMtlRow.MtlPartNum
			&& r.MtlSeq != ecoMtlRow.MtlSeq)
		.Any();
	
	if (dbExists)
	{
		InfoMessage.Publish("Fancy message here.");
	}
}
5

i would probably use a “COUNT” feature in the query. that way you can simply look in the ecomtl table and find out if the count in the table for the modified part is greater than 1… if so, throw the warning message, or do more work then to search for and tell the user which material line(s) also have this part number. using Count reduces the impact on the search, because it only has to return one value, rather than an entire data record.

2 Likes
6

Could replace the Any for a Select / ToList for a nicer message, yeah.

var mtlSeqs = Db.ECOMtl
    .Where(...)
    .Select((r) => r.MtlSeq)
    .ToList();

if (mtlSeqs.Count > 0)
{
    var niceMsg = "This part appears in these materials: " + string.Join(", ", mtlSeqs);
    InfoMessage.Publish(niceMsg);
}

Is there a big difference between Any and Count? I guess it’d be better to use count, you can optionally put that in the message, but neither one should return an entire row.

7

there is a slight difference in what sql does… for “.any()”, the system will stop looking after it finds one record. for “.count()”, the system will still need to find all occurances before it can stop to provide the count. so if you only want to know if there is at least one, then .any is better, but if you want to tell them how many, then .count is better.

1 Like