XML API FinishedAssemblies

Ideas (en)
, ,
1

Looking for a traceability report on serial or batch numbers, which currently isn’t available in Exact Online, I tried something in PowerBI. The Exact Online REST API won’t give me the information I need, but the XML API for FinishedAssemblies seems useful.

But, the Invantive PowerBI Connector doesn’t return all information available in the XML.

Can this be changed?

This is my XML export from Exact Online:

<?xml version="1.0" encoding="UTF-8"?>
<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">
  <FinishedAssemblies>
    <FinishedAssembly assemblynumber="8" ordernumber="214">
      <AssemblyDate>2020-11-08</AssemblyDate>
      <Quantity>3</Quantity>
      <SerialNumbers>
        <SerialNumber SerialNumber="FP-04" Blocked="0">
          <Parts>
            <Part>
              <Item ID="{xxx}" code="XXX_RM3">
                <Description>XXX raw material 3</Description>
              </Item>
              <SerialNumbers>
                <SerialNumber SerialNumber="RM3-04" Blocked="0" />
              </SerialNumbers>
            </Part>
            <Part>
              <Item ID="{xxx}" code="XXX_SF">
                <Description>XXX semifinished product</Description>
              </Item>
              <SerialNumbers>
                <SerialNumber SerialNumber="SF-04" Blocked="0" />
              </SerialNumbers>
            </Part>
          </Parts>
        </SerialNumber>
        <SerialNumber SerialNumber="FP-03" Blocked="0">
          <Parts>
            <Part>
              <Item ID="{xxx}" code="XXX_RM3">
                <Description>XXX raw material 3</Description>
              </Item>
              <SerialNumbers>
                <SerialNumber SerialNumber="RM3-03" Blocked="0" />
              </SerialNumbers>
            </Part>
            <Part>
              <Item ID="{xxx}" code="XXX_SF">
                <Description>XXX semifinished product</Description>
              </Item>
              <SerialNumbers>
                <SerialNumber SerialNumber="SF-03" Blocked="0" />
              </SerialNumbers>
            </Part>
          </Parts>
        </SerialNumber>
        <SerialNumber SerialNumber="FP-02" Blocked="0">
          <Parts>
            <Part>
              <Item ID="{xxx}" code="XXX_RM3">
                <Description>XXX raw material 3</Description>
              </Item>
              <SerialNumbers>
                <SerialNumber SerialNumber="RM3-02" Blocked="0" />
              </SerialNumbers>
            </Part>
            <Part>
              <Item ID="{xxx}" code="XXX_SF">
                <Description>XXX semifinished product</Description>
              </Item>
              <SerialNumbers>
                <SerialNumber SerialNumber="SF-02" Blocked="0" />
              </SerialNumbers>
            </Part>
          </Parts>
        </SerialNumber>
      </SerialNumbers>
    </FinishedAssembly>
  </FinishedAssemblies>
</eExact>

The current Invantive PowerBI Connector doesn’t support the SerialNumbers node. Can it be changed to a set of records like this:

assemblynumber ordernumber AssemblyDate Quantity SerialNumberParent code SerialNumberChild Blocked
8 214 2020-11-08 3 FP-04 XXX_RM3 RM3-04 0
8 214 2020-11-08 3 FP-04 XXX_SF SF-04 0
8 214 2020-11-08 3 FP-03 XXX_RM3 RM3-03 0
8 214 2020-11-08 3 FP-03 XXX_SF SF-03 0
8 214 2020-11-08 3 FP-02 XXX_RM3 RM3-02 0
8 214 2020-11-08 3 FP-02 XXX_SF SF-02 0
2

When I understand the question correctly, the repeating elements are available of:

  • /FinishedAssemblies/FinishedAssembly

But you are missing the elements from the following repeating elements, each which would be a separate table:

  • /FinishedAssemblies/FinishedAssembly/SerialNumbers/SerialNumber
  • /FinishedAssemblies/FinishedAssembly/SerialNumbers/SerialNumber/Parts/Part
  • /FinishedAssemblies/FinishedAssembly/SerialNumbers/SerialNumber/Parts/SerialNumbers/SerialNumber

Is that correct?

3

Yes. I need the Parent SerialNumbers with their Child SerialNumbers. If they are in separate tables, there needs to be a relationship between Parent and Child. My suggestion would be a record per Parent-Child combination.

4

The next BETA of 20.1 will add the following:

  • FinishedAssemblySerialNumbers
  • FinishedAssemblySerialNumberParts
  • FinishedAssemblySerialNumberPartSerialNumbers

However, the XML APIs of Exact Online have an excessive number of columns as you probably know, since on Exact Online there is no way to determine what XML elements can contain a value given the design of the Exact Online XSD. A sheer unlimited range of variants is documented but only few columns contain a value typically.

This leads to a problem with the last two new tables:

itgenxml088 Optimize current excessive number 1.940 of columns of object FinishedAssemblySerialNumberPartSerialNumbers…

For completion of the change, we need your assistance by temporary access to a company filled with data. The easiest way to do this is to schedule a session to jointly log on using the next BETA of 20.1. The software automatically detects paths typically empty and gives hints to reduce the number of columns in these Exact Online XML tables.

As an alternative, we would appreciate it highly when you could provide us with a refresh token for support use using our pre-authentication tool for support purposes on Exact Online to generate a refresh token. Once optimized, the client authentication can be revoked in the Exact Online security center. After that, the change will typically be available within 2 weeks on Invantive Cloud and in the BETAs of the on-premise products within one week.

5

I have sample data in our demo Exact Online Wholesale Advanced environment. We can schedule a session to look at the data. I could also provide you with an XML export file that shows which data is used.

6

Thanks for your assistance in the process. The three new XML tables for finished assemblies will be available starting 20.1.277 BETA and the next Invantive Cloud release, including the second nesting level of serial numbers (FinishedAssemblySerialNumberPartSerialNumbers). There will be no inclusion on 20.0.