Solved

How to include ecommerce External ID for product in GI

  • 23 December 2022
  • 12 replies
  • 113 views
M
Rank
Userlevel 5
Badge +1

I’d like to be able to create a GI that displays product Inventory IDs and their corresponding Shopify Product ID. I know that data is stored somewhere in Acumatica because it is available in the Sync History (BC301000) screen and actually hyperlinks to the product in the Shopify admin.

Does anyone know what table stores this External ID data in Acumatica? And how it can be related to the InventoryItem table?

icon

Best answer by mikeho 23 December 2022, 22:21

View original

12 replies

aaghaei
Rank
Userlevel 7
Badge +8

@mikeho if you hold control and alt keys on your keyboard together (you will notice small ? Mark with your mouse pointer) and click on any field, you will see the field information including the DAC holding the field. You need customizer role permissions to do so

As far as I know In synchronization tables Acumatica only keeps the reference to other add-on records and not the full recordset.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
M
Rank
Userlevel 5
Badge +1

@aaghaei - oddly enough, I had tried that originally and it wasn’t working for me, but I just tried again and it worked this time. So I can see that the field I’m looking for is in the BCSyncStatus table:

 

However, I’m not sure how to connect it to the InventoryItem table. Any suggestions?

M
Rank
Userlevel 5
Badge +1

After some playing around I figured it out:

 

aaghaei
Rank
Userlevel 7
Badge +8

@mikeho i have not worked with the BigCommerce related tables but if you have SQL access then you can run a select on BCSyncStatus otherwise export this DAC using a GI to excel and search for one of the inventory items. Then logically you should see a record type or similar field that you will use as filter in your GI link and you should see an AcumaticaID or InternalID (or something similar) that you will link it to the InventoryItem table. I guess this from how Acumatica connects to ProCore.

hope this helps otherwise someone with exact BC experience should help.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +8

On a second though the link could be by NoteID of the InventoryItem to a field in BCSyncStatus

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
M
Rank
Userlevel 5
Badge +1

@aaghaei - yeah, that’s what I had originally thought and tried but it wasn’t working (linking by NoteID). Then I took a look at all the fields in the BCSyncStatus table and discovered a field called LocalID that looked similar to the NoteID; I was able to link that to the NoteID of the InventoryItem and produce the desired results.

B
Rank
Userlevel 4
Badge

Hi @everyone  
@mikeho 

Can you check what I missed in my generic inquiry? I also need to add External ID from Bigcommerce to the Stock Items screen of Acumatica but mine is not working.
 


​​​​

errror occured as a result.

M
Rank
Userlevel 5
Badge +1

Hi @bernadeth58 - that looks correct to me (at least that matches what I am using in my GI). I will say, we are using Shopify, not BigCommerce, in case that makes a difference here.

What is the error message you’re getting?

B
Rank
Userlevel 4
Badge

Hi @mikeho 

This is the error I got.

 

M
Rank
Userlevel 5
Badge +1

Hi @bernadeth58 - If you disable that last Table Relation and the ExternID field from your Results, does your report load without error? If so, then it would seem that there is a difference in field types between the BigCommerce localID and the Acumatica noteID and they can’t be joined like they can with Shopify.

B
Rank
Userlevel 4
Badge

Hi @mikeho yes this is the error occured
 

 

M
Rank
Userlevel 5
Badge +1

Hi @bernadeth58 - if you’re still getting this error message after you deactivated that Table Relationship and Results field, then it looks to me like there’s an issue with one of your other table joins, where you’re attempting to join a field of one data type to a field of a different data type.

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