Solved

Include Segmented Keys into Inquiry

  • 2 May 2023
  • 6 replies
  • 122 views
K
Rank
Userlevel 5
Badge +1

Hi All,

 

I am currently working on an Inquiry for a Dashboard where I need to group by a certain Segment.

I did it once before with Inventory Subaccounts:
 

 

 

Yet, now I am working with Projects and the Contract CD Field is segmented.
I tried it this way, yet get the error that PMProject.ContractCD_Segment1 is not existing.

Does anybody know how to properly incorporate the Segmented Keys here?

 

If this helps, the Alias is referring to the CS.Segment Table

icon

Best answer by hkabiri 2 May 2023, 19:47

View original

6 replies

K
Rank
Userlevel 5
Badge +1

Quick Update. I think I must’ve done something wrong but the new Error states this: 

The multi-part identifier "Contract.ContractCD" could not be bound. The multi-part identifier "Contract.ContractCD" could not be bound.

hkabiri
Rank
Userlevel 6
Badge +5

@krausef77  In most cases this error means you have bad table relations. Contract Table has ContractID as the key and it should be added to relations in case the Contract table Data needs to be retrieved.

You can share your GI xml file so we can take a closer look if issue not resolved.

SAM KABIRI | L2 Support Engineer | hkabiri@acumatica.com | www.acumatica.com
iqraharrison48
Rank
Userlevel 7
Badge +6

@krausef77 one thing that i can see from your screen shots is that you used left join for your inventory subaccount segment and you are using inner for the project one.  maybe change that to left?  it will be helpful to have the file as well so if that doesn’t work, please share

thanks!

Iqra ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Laura02
Rank
Badge +18

Hello,

Are you able to include the entire ContractCD in one column of your GI, then use a calculated column “Segment 1” in another column of the GI to obtain the contract ID segment you need?  For example LEFT([Contract.ContractCD],3), and then Group by and aggregate based on the calculated field “Segment 1”?

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
craig2
Rank
Userlevel 4
Badge +1

Hi there,

It’s a small thing, but you may want to try SegmentID = ‘1’, with the quotes.  That’s what I’ve had to do when doing joins at the Segment-level.

JTang
Rank
Userlevel 3

 

Quick Update. I think I must’ve done something wrong but the new Error states this: 

The multi-part identifier "Contract.ContractCD" could not be bound. The multi-part identifier "Contract.ContractCD" could not be bound.

When we do table joins the CD generally is a substitute key for the ID of the DAC, therefore it should be ContractID_Segment1 if it exists that is. If it doesn’t exist, then I would just use Substring function to calculate it. 

Here’s an example if the we want to start from position 0 (starting position) with a length of 3. That should resolve your issue. 

=Substring([PMProject.ContractCD],0,3) 

Thanks! - Johnny Tang

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved