I am having an issue with a join in a search. I ha...
# suitescript
w
I am having an issue with a join in a search. I have created a search in the UI that does what I need it to do but when I convert this over to my script I start getting null results for the join fields.
Copy code
var createSaveSearch = search.create({
                    type: "customer",
                    filters:
                        [
                            ["isinactive","is","F"],
                            "AND",
                            ["internalid","anyof","11980"]
                        ],
                    columns:
                        [
                            search.createColumn({name: "internalid", label: "Internal ID"}),
                            search.createColumn({name: "entityid", join: "contact", label: "Name"})
                        ]
                })
It's not bringing back the contact names.
e
where is the rest of the code?
m
Your search looks correct. I would check the following. 1. Make sure the customer with id 11980 does in fact have a contact. 2. Make sure that entityid field is filled on that particular contact. 3. Try reversing the join by searching the contacts and joining to customer.
w
@ehcanadian here is the complete code.
Copy code
/**
 * @NApiVersion 2.x
 * @NScriptType UserEventScript
 * @NModuleScope SameAccount
 */

define(['N/runtime', 'N/record', 'N/search', 'N/config', 'N/ui/serverWidget' ],
    function(runtime, record, search, config, ui)
    {
        function beforeLoad(context)
        {


                var form = context.form; //ui.createForm({ title: 'Select Field from Search' });

                var formField = form.addField({id: 'custpage_saved_searches',
                    type: 'select',
                    label: 'List of contacts',
                //    container: 'salesorders'
                });

                var objSavedSearches = getSavedSearches();

            log.debug({
                title: 'objSavedSearches.length',
                details: objSavedSearches.length
            });

                for (var i = 0; i < objSavedSearches.length; i++) {
                    if (i == 0)
                        // formField.addSelectOption({
                        //     value: "",
                        //     text: "",
                        // });
                    formField.addSelectOption({
                        value: objSavedSearches[i].id,
                        text: objSavedSearches[i].name,
                    });
                }


            function getSavedSearches() {
                var createSaveSearch = search.create({
                    type: "customer",
                    filters:
                        [
                            ["isinactive","is","F"],
                            "AND",
                            ["internalid","anyof","11980"]
                        ],
                    columns:
                        [
                            search.createColumn({name: "internalid", label: "Internal ID"}),
                            search.createColumn({name: "entityid", join: "contact", label: "Name"})
                        ]
                })
                resultObj = []
                var saveSearchResult = createSaveSearch.run()
                saveSearchResult.each(function (item) {
                    obj = {}
                    obj.id = item.getValue({name: "internalid"})
                    log.debug({
                        title: 'obj.id',
                        details: obj.id
                    });
                    obj.name = item.getValue({name: "entityid"})
                    log.debug({
                        title: 'obj.name',
                        details: obj.name
                    });
                    resultObj.push(obj)
                    return true
                })
                log.debug({
                    title: 'resultObj',
                    details: resultObj
                });
                return resultObj;
            }
        }

        return {
            beforeLoad : beforeLoad
        };

    });
e
You need the
join
in your
item.getValue({name: 'entityid', join: 'contact'})
w
@Marvin ID 11980 has two contacts. Now the odd thing is that one contact is actually from another company, and that is why I have to create the join this way and not reversing it. I need the ability to have contacts from other companies attached to some of our customers.
m
See @ehcanadian comment. You're doing the search correct, but not fetching correct when your doing
.getvalue
.
w
@ehcanadian Thanks for that.. Guess I figured the search was bringing the value back, that I didn't need the join.. @Marvin Thanks for the help.