BAQ ~List~ handling

Kinetic ERP
1

Are there any BAQ tricks to return a tilde delimited list column?

For example, I believe this is valid SQL (being SaaS I cannot test) that should return tilde delimited column of VendorNums.

BAQ complains ‘possible sql injection’ probably because SELECT and or FOR XML are not supported in calculated columns.

SELECT 
	[VendPart].[Company] as [VendPart_Company], 
	[VendPart].[PartNum] as [VendPart_PartNum], 
	(STUFF  
    (  
        (  
        SELECT DISTINCT '~'+ CAST(V.VendorNum AS VARCHAR(MAX))  
        FROM Erp.VendPart as [V], Erp.Part as [P]
        WHERE V.PartNum = P.PartNum AND P.PartNum = VendPart.PartNum
        FOR XML PATH('')  
        ),1,1,''  
    )) as [Calculated_VenNums] 

FROM Erp.VendPart as [VendPart]
GROUP BY	
    [VendPart].[Company], 
	[VendPart].[PartNum]

Is there another way to return list columns in BAQ?

PS - I believe I’ve seen mentioned on here that SQL List functions exist in the database. If so, are these accessible in a BAQ?

PSS - Sooo wish I had a copy of the db schema.

2

Search for string_agg should be what you need

4 Likes
3

Thanks.
To self: It’s been there since 2017, Josh
tune in come on GIF by Discovery Europe

3 Likes
4

I think there is a stored procedure for use too.

@Banderson ?

5

Yeah, to join fields, string_agg() is the way to go. XML path is the old hack and it disallowed in newer versions of Epicor.

@klincecum I think you’re thinking of splitting delimited strings, and that’s Ice.Entry().

2 Likes
6

Can we use ERP.ListEntry() T-SQL function in BAQ?

Here’s where I saw it mentioned:

PS - any on-prem’rs wanna share a db create script or .dacpac of 2025.1 db?

1 Like
7

You should be able to. The scalar function is there. I’ve never use it myself.

2 Likes
8

Thanks. Now to guess at it’s signature cuz I’m Saas-blind
season 1 friends GIF

9

image

image
image407×307 7.28 KB

Looks like it’s the same is Ice.Entry()
image

2 Likes
10

Thanks. I thought maybe you were referring to c# w Ice.Entry().

Any idea whether @idx is zero or 1 based? Guessing 1-based.

11

0 based.

image
image420×284 7.42 KB

image
image190×228 2.11 KB

1 Like
12

I thought there was one for the other way.

13

That’s string_agg().

What am I missing here?

here’s the list of scalar functions. (the blurred out one is one we added)

image
image282×501 6.57 KB

1 Like
14

stop making me jealous @Banderson and send .dacpac. :wink:

and Kevin:

image
image476×268 26.3 KB

/jk/jk

15

Conan Obrien I Dont Know What That Is GIF by Team Coco

might need some help do to that.

16

looks like theres not a entriesToList() that explodes delim string to rows. That’d be cool.

17

Yeah, I don’t see anything like that, and I’m pretty sure it would break the BAQ engine. Adding in columns dynamically isn’t something historically that’s been a capability.

1 Like
18

Probably won’t work, but a .dacpac is a file made by SQLPackage utility (built-into SSMS, SSDT, ADS, & MSSQL VSCode Extension) which can be used to create an empty database. Basically it’s the publish output of a VS Database Project.

image
image691×786 29.9 KB

It’d be swell if Epicor made, say EDU db backup available for SaaS customers to install on-prem. Some of us are at home in T-SQL (even if we forget the newish features like STRING_AGG [:rolls-eyes-at-self])

1 Like
19

Maybe nothing, I just swear I read something about it.

I”ve been wrong before, I’m sure it can happen again.

1 Like
20

Hi Josh,
If I understand correctly what you are trying to achieve, there is actually a SQL function to extract one value in an Epicor ~-separated List.
This is PARSENAME(). It uses dot as default separator but you can easily replace the ~ symbol by dots before running PARSENAME(). So you will be able to easily extract one value (or more) in an Epicor list like this.
Sorry I’m French so if this isn’t clear, please let me know.
I often use this kind of syntaxes for BI analysis on Epicor with a third-party tool, it works like a charm.

Regards,
William

2 Likes