Part Number BAQ

Nstanley · April 25, 2025, 2:22pm

Hello, I have a question about creating a BAQ to display part numbers that have NOT been generated yet. As you can see in the picture below, we have a lot of part numbers that have gaps between them (such as between part 264662-265000). Instead of adding onto the list, we would like to start filling those gaps when we do add new parts.

Is there a way to create a BAQ to display those gaps or “available” spaces between existing part numbers? I haven’t had much luck and maybe there’s an easier way to generate that list than a BAQ. Any advice is greatly appreciated.

Thank you

Connor_St_Louis · April 25, 2025, 2:36pm

Hi Nick,

This could potentially be done with a dashboard built for this purpose -
My thoughts would be to have a simple button that, when clicked, will call our function which will handle all of this.

Functions are very powerful and I use them quite frequently, we can add this as a component in an event within our application/dashboard. For e.g:

Utilizing Epicor Functions Maintenance, we can then declare what the function will do. In this case, it might be best to return a parameter of type System.Data.DataSet as this can easily be saved into a Data View for display within a grid component on our application.

Here is a very basic function that takes the partnum as a input parameter and returns the associated description. In your function, I would assume what we would want to do is - for the range of appropriate part numbers - we want to query GetByID to see if the part already exists. If it does, skip and go to the next number. If the part does not exist, let’s add this partnum to our output DataSet as a partnum that is available for use.

My basic function:

desc = "";

this.CallService<Erp.Contracts.PartSvcContract>(PartService => {
    try {
        var retrievedPart = PartService.GetByID(part);
        if (retrievedPart.Part.Any()) {
            foreach (var row in retrievedPart.Part) {
                desc = row.PartDescription;
            }
        }
    } catch {
        desc = "";
    }
});

*In my example, desc is the name of my output parameter of type String and ‘part’ is my input param which is also a String. This is to simply show the syntax for calling epicor services within a function.

Also make sure that our function library has the proper access:

and if querying:

NateS · April 25, 2025, 3:05pm

Can we assume your part numbers are integers? Although the part number field is a string, if you have rules in place to ensure it stays an integer, then we might be able to do something.

I tapped AI to solve this one in Excel. First export your list of part numbers (assuming they are integers). Paste them in Excel columnA. In column C put in this formula:

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))), A:A, 0)), ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))), ""), ROW()-1), "")

Then copy it down column C until you see all the values between the upper and lower limits found in column A. It reports only the unused numbers in the range.

For my own sanity, this is what each part of that wild formula does:

`MIN(A:A)` and `MAX(A:A)`

These get the smallest and largest part numbers in Column A.

Example: If A2:A6 contains 1001, 1002, 1004, 1005,
then MIN(A:A) = 1001, and MAX(A:A) = 1005.

`ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))`

This creates an array of all numbers from the min to max.

Same example: this gives {1001,1002,1003,1004,1005}.

`MATCH(..., A:A, 0)`

This tries to match each of those numbers against the values in Column A.
If a number is found, it returns its position; if not, it returns #N/A.

`ISNA(MATCH(...))`

Wraps the match to check which values are missing from Column A.

Output: {FALSE, FALSE, TRUE, FALSE, FALSE} — meaning 1003 is missing.

`IF(..., ROW(...), "")`

This says:

“If the number is missing (ISNA = TRUE), return it.
Otherwise, return blank (”“).”

So, it returns { "", "", 1003, "", "" }.

`SMALL(..., ROW()-1)`

This takes the nth smallest non-blank value from that list.

ROW()-1 is used to get 1, 2, 3, etc., as you drag the formula down.
So the first row gets the 1st missing number, the second gets the 2nd, and so on.

`IFERROR(..., "")`

Finally, if there are no more missing numbers, it just returns a blank instead of an error.

Nstanley · April 25, 2025, 3:55pm

I like this a lot more, however some of our part numbers aren’t just integers. I am currently trying to build a subquery that filters only the part numbers that are int. but with minimal luck, it’s still pulling part numbers that aren’t specifically 6-digit numbers. So, your answer would be great to use if I can figure out how to filter out the part numbers that aren’t Int. I do however know that the part list that we are trying to fill starts at 211008 and ends at 990504. I’m aware this is a very large range, so I was initially trying to do it by 10,000 to see if it even works.

Once that is done, I can copy and paste into excel with the list of part #'s provided by the query and run the script you provided.

NateS · April 25, 2025, 4:36pm

If your part numbers are not restricted to integers, this becomes a drastically more difficult problem to solve. It would be easier if you defined the expected ranges. Without strictly defined part numbers, the range for possible values is extremely large.

As for identifying part numbers that are not integers. Try this calculated field in a BAQ.

IIF(TRY_CAST(Part.PartNum AS INT) IS NOT NULL, 1, 0)

hmwillett · April 25, 2025, 8:50pm

Here you go. Run this function on Part.GetNewPart, post-processing (or whatever the BO is).

AutoSequence.efxb (2.8 KB)

No Excel needed. Make Epicor do the work.

You could also just paste this code into an Execute Code widget in the BPM instead of calling the function.

Function Code

try
{
  // Get a list of part numbers in the system
  var partNumList = (
    from p in Db.Part
    where p.Company == Session.CompanyID
    select p.PartNum).ToArray();
  
  // Was used for testing
  // var partNumList = inDemoArray.Split('~').ToArray();
    
  if( partNumList.Count() > 0 )
  {
    for( int i = 0; i < partNumList.Count(); i++ )
    {
      int curPart = 0; // Change this to be your desired starting value - 1
      int nextPart = 0; // Change this to be your desired starting value - 1
      
      Int32.TryParse(partNumList[i], out curPart);
      
      // If i + 1 is valid, then parse, else return junk
      if( i + 1 < partNumList.Count() )
        Int32.TryParse(partNumList[i + 1], out nextPart);
      else
        nextPart = -1;
      
      // Check to see if nextPart is sequential, if not, return next sequential part
      if( (curPart + 1) != nextPart )
      {
        outPart = (curPart + 1).ToString();
        Success = true;
        return;
      }
    }
  }
}
catch(Exception e)
{
  Success = false;
  ErrorMsg = e.Message.ToString();
}

Modified for BPM Post-Processing

try
{
  var tt = ds.Part.Where(r => r.Added()).FirstOrDefault();
  
  if( tt != null )
  {
    // Get a list of part numbers in the system
    var partNumList = (
      from p in Db.Part
      where p.Company == Session.CompanyID
      select p.PartNum).ToArray();
      
    if( partNumList.Count() > 0 )
    {
      for( int i = 0; i < partNumList.Count(); i++ )
      {
        int curPart = 0; // Change this to be your desired starting value - 1
        int nextPart = 0; // Change this to be your desired starting value - 1
        
        Int32.TryParse(partNumList[i], out curPart);
        
        // If i + 1 is valid, then parse, else return junk
        if( i + 1 < partNumList.Count() )
          Int32.TryParse(partNumList[i + 1], out nextPart);
        else
          nextPart = -1;
        
        // Check to see if nextPart is sequential, if not, return next sequential part
        if( (curPart + 1) != nextPart )
        {
          tt.PartNum = (curPart + 1).ToString();
          InfoMessage.Publish(curPart.ToString());
          return;
        }
      }
    }
  }
}
catch(Exception e)
{
  InfoMessage.Publish(e.Message.ToString());
}

kve · April 26, 2025, 3:22am

This will definitely get the job done. I did notice that the BPM version here might cause you an issue by setting the PartNum as part of GetNewPart. You lose some of the default settings (Units of Measure, for example) by doing that instead of calling ChangePartNum. You can avoid that with a few tweaks, though.

If you put the code in a Conditional widget and then Invoke the Part.ChangePartNum BO Method, it will pull the Part defaults and still set the partnum you’re looking for.

Here’s the code for the conditional widget:

if ( !ds.Part.Any(x => x.Added()) ) return false;

int StartPN = 100000; // Starting PartNum
int parsed = 0;       // Placeholder

try
{
    var IntParts = Db.Part.Where( x => x.Company == Session.CompanyID ).Select( s => s.PartNum ).ToList() // List of PartNums (String)
                        .Where( x => int.TryParse(x, out parsed) ).Select( s => parsed ) // Ints only
                        .Where( x => x >= StartPN ); // Higher than StartPN

    int MaxPN = IntParts.Max(); // Highest Int PartNum

    newPN = Enumerable.Range( StartPN, MaxPN ).Except( IntParts ) // Missing Ints in Range
                    .DefaultIfEmpty(MaxPN+1) // If no gaps, highest int +1
                    .Min().ToString();       // Select Lowest int from List
    return true;
}
catch ( Exception e )
{
    return false;
}

Part Number BAQ

MIN(A:A) and MAX(A:A)

ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))

MATCH(..., A:A, 0)

ISNA(MATCH(...))

IF(..., ROW(...), "")

SMALL(..., ROW()-1)

IFERROR(..., "")