In the query module how do I go about joining to a...
# suitescript
j
In the query module how do I go about joining to a custom record attached to the item record? Here's my code
s
@jacksonp The first join when creating a query cannot be an autojoin, it needs to be explicit (I have no idea why). I don't know the exact syntax you need but its probably close to
Copy code
var memberJoin = kitQuery.joinTo({
     fieldId: 'member',
     target: 'memberitem'
})
If you make the query in the UI, and the save it. You can create a second query on saved workbooks and can get the xml version of the query (or there might be a more sane way to do it). You should be able to get the needed
target
from the xml object it provides for you if
memberitem
is not correct.
Additionally, the component joins are not necessary in code at all. You can add the string representation of them to your result or criteria columns and that will work fine.
Copy code
var queryMain = query.create({
    type: query.Type.TRANSACTION
});
queryMain.condition = queryMain.and(
    queryMain.createCondition({
        fieldId: 'type',
        operator: query.Operator.ANY_OF,
        values: ['TrnfrOrd']
    }),,
    queryMain.createCondition({
        fieldId: 'transactionlines.item^item.locations.location',
        operator: query.Operator.ANY_OF,
        values: fromLocation
    })
)

queryMain.columns = [
    queryMain.createColumn({
        fieldId: 'transactionlines.item'
    }),
    queryMain.createColumn({
        fieldId: 'transactionlines.units'
    }),
    queryMain.createColumn({
        fieldId: 'transactionlines.units',
        context: query.FieldContext.DISPLAY
    }),
    queryMain.createColumn({
        fieldId: 'transactionlines.rate'
    })
]
Somehting like this for example will run perfectly fine