Accessing Data from Multiple Tables

For any table that has a one‐to‐many relationship with the table being queried, its data can be accessed by prefixing the object name to the field name, such as ObjectName.FieldName.

For example, when querying the PaymentTransaction table, we can get related Location, Facility, Contract, Vendor (Employer), and Organization data. This is typically indicated by the presence of Foreign Keys; for example, the PaymentTransaction table has ContractID and OrganizationID fields.

This is because the Location, Facility, and Contract tables are parent tables to the PaymentTransaction table, and Employer and Organization tables are sibling tables. In this example, you can use:

Contract.ClientEntityID: Get the Contract ID because the payment transaction is always associated with one Contract.
Employer.APVendorNumber: Get the vendor number because the payment transaction may only be associated with one Vendor.
Location.LocationName: Get the Location’s name because the payment transaction is always associated with one Contract that is always associated with one Location.

If you need to get data from multiple fields, we recommend that you create a report and call it directly with the API. This will reduce the complexity of your call and reduce the likelihood of errors.

Example

Method: GET

URL: {{url}}/rest/businessObject/PaymentTransaction/details?fiql=EffectiveDate=ge=2017‐05‐01;EffectiveDate=lt=2017‐06‐01&fields=Contract.ClientEntityID,Employer.APVendorNumber,Location.LocationName

XML Response:

Copy

<?xml version="1.0" encoding="UTF‐8"?>
<LxERP>
    <PaymentTransaction lxID="50282770">
        <Contract.ClientEntityID>00226</Contract.ClientEntityID>
        <Employer.APVendorNumber>SIMO60686‐RENT0226</Employer.APVendorNumber>
        <Location.LocationName>00226‐MAIN1‐MCALLEN, TX</Location.LocationName>
    </PaymentTransaction>
    <PaymentTransaction lxID="50282762">
        <Contract.ClientEntityID>02203</Contract.ClientEntityID>
        <Employer.APVendorNumber>GFVA9901‐RENT2203</Employer.APVendorNumber>
        …
</LxERP>