Is Infinitely Dynamic Recursion Possible in BAQs?

Epicor ERP 10
,
1

I’m using the UD03 table to hold a relationship between Parent (upstream) and Child (downstream) jobs.
image

As seen here, with PHP, I can do any number of loops from any focused job in the chain, going both upstream and downstream, finding any and all linked jobs recursively.

image
image1034×872 56.9 KB

[Problem:] I want to be able to give a BAQ one job number in the chain and have it return all the linked jobs so my users can easily load the whole set into the Job Scheduling Board, Job Entry, etc.

Has anyone had luck with recursive loops in BAQs that could have any number of levels? Is it even possible?

Any other creative ideas to the [Problem] above are welcome. (For example, I can easily get the job numbers into a delimited parameter string, but I haven’t been able to get the BAQ to parse it into a selection.)

1 Like
2

You can make a CTE subquery to do recursion. Here’s an example with menus.

1 Like
3

And another example (long thread) which sounds close to what you’re trying to do (I think)

~If you want to recursively traverse the BOM you may need a CTE query?~ - Kinetic 2021 - Epicor User Help Forum (epiusers.help)

3 Likes
4

Thank you! I’ll give it a whirl.

1 Like
5

As others have said… CTE is the answer… basically, it will take a three query BAQ:

  1. Query 1: CTE query that selects the “anchor” record to start the cycle
  2. Query 2: UNION ALL query that gathers all the records… this query uses query 1 as the anchor and gathers each layer, and will continue to do this until there are no additional layers
  3. Query 3: TOP query that displays the above information.

Now that the CTE and UNIONALL queries must have the same column types (a requirement of CTE). but the TOP can have additional data that is gathered after you have retrieved all the details from the first two queries.

1 Like
6

Thank you for that breakdown, Tim. I appreciate your time!