Invalid query detected, please check logs for details [Unknown field item in table sc_req_item]

[DEV 1]

Invalid query detected, please check logs for details [Unknown field item in table sc_req_item]

this error is shown as warning in logs, how to debug this

schedule script is not working

[Mark Roethof]

Hi there,

Can you share details of your script?

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020-2022 ServiceNow Community MVP
2020-2022 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list

[Mark Roethof]

Also double check the field mentioned. Out-of-the-box, there's no item field. Yeah there's a field with label "Item", though that's actually "cat_item".

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020-2022 ServiceNow Community MVP
2020-2022 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list

[DEV 1]

This is script of schedule script which is not execuating

but when this same script i execuated from fix script it works fine

var cnt = 1;
var csvContent = '"RITM","InventoryNo","LocationNo","DefaultIssueUOM","DefaultIssueConversionFactor","DefaultCountUOM","DefaultCountConversionFactor","Cost","IsBillable","IsTaxable","ItemType","PriceMarkup","PriceMarkupType","DisablePurchasing","MinQuantity","MaxQuantity","SafetyStock","BinShelf","AssetLedgerNo","ExpenseLedgerNo","SyncFlag","LocationUOM","LocationConversionFactor","CrossReferenceNo","InventoryGroupNo","InventoryGroupName","InventoryDescription","StockUOM","UNSPSCCode","DefaultAssetLedgerNo","DefaultExpenseLedgerNo","Classification","Classification2","PerioperativeItemCategory","HCPCSCode","ARBillingCode","IsLatex","SystemType","DefaultIsBillable","Priority","FacilityNo","FacilityName","VendorNo","VendorName","VendorItemNo","VendorUOM","VendorConversionFactor","VendorCost","ContractNo","ContractExpiration","ManufacturerNo","ManufacturerName","ManufacturerItemNo","GTIN","NDCNumber","LockCost","ActiveStatus"\r\n';

// locations with 200% markup
var markuparray = ["5601", "5606", "5608", "5617", "5622", "5615", "5609", "5631", "5619", "5610", "5604", "5624", "5612", "6001", "7201", "9004"];
gs.info("outside loop");

var rec = new GlideRecord('sc_req_item');
rec.addQuery('assignment_group', '0cfdca83db521bc0babf79fdae9619f9'); // SP Materials Management
rec.addQuery('u_export_state', 'ready for export');
rec.addQuery('item', '4514ea12db481c104f545e38dc9619b5'); // ENVI Item Add Form
rec.query();
while (rec.next()) {
    gs.info("this is for test"+"  "+rec.number);
    var a = '';
    var loc = new GlideRecord('cmn_location');
    loc.addQuery('sys_id', rec.variables.dv_location);
    loc.query();
    while (loc.next()) {
        var location = loc.u_financial_code;
        var master = location;
        if (location == '552-0000') {
            master = 'Master';
        }
    }
    var spec = new GlideRecord('u_unspsccode');
    spec.addQuery('sys_id', rec.variables.unspsccode);
    spec.query();
    while (spec.next()) {
        var u_expenseledgerno = spec.u_expenseledgerno;
        var u_arbillingcode = spec.u_arbillingcode;
        var u_unspsccode = spec.u_unspsccode;
        var u_classification = spec.u_classification;
        var u_classification2 = spec.u_classification2;
    }
    csvContent += '"' + rec.number + '","","552","EA","1","EA","1","' + rec.variables.cost + '","FALSE","FALSE","Stock","200","%","FALSE","","","","","' + rec.variables.asset_ledger_no + '","' + u_expenseledgerno + '","TRUE","EA","1","","1","Item Master","' + rec.variables.description + '","EA","' + u_unspsccode + '","' + rec.variables.asset_ledger_no + '","' + u_expenseledgerno + '","' + u_classification + '","' + u_classification2 + '","None","","' + u_arbillingcode + '","FALSE","Standard","FALSE","","Master","' + rec.variables.facility_name + '","' + rec.variables.vendor_no + '","' + rec.variables.vendor_name.getDisplayValue() + '","' + rec.variables.vendor_catalog_no + '","' + rec.variables.unit_of_measure + '","' + rec.variables.conversion_factor + '","' + rec.variables.price_per_uom + '","' + rec.variables.contract_no + '","' + rec.variables.contract_expiration + '","' + rec.variables.manufacturer_no + '","' + rec.variables.manufacturer.getDisplayValue() + '","' + rec.variables.manufacturer_catalog_no + '","","' + rec.variables.national_drug_code + '","FALSE","TRUE"\r\n';

    var markup = "";
    if (markuparray.includes(location)) {
        markup = 200;
    }
    csvContent += '"' + rec.number + '","","' + location + '-100","EA","1","EA","1","' + rec.variables.cost + '","FALSE","FALSE","Stock","' + markup + '","%","FALSE","","","","","' + rec.variables.asset_ledger_no + '","' + u_expenseledgerno + '","TRUE","EA","1","","1","Item Master","' + rec.variables.description + '","EA","' + u_unspsccode + '","' + rec.variables.asset_ledger_no + '","' + u_expenseledgerno + '","' + u_classification + '","' + u_classification2 + '","None","","' + u_arbillingcode + '","FALSE","Standard","FALSE","","' + master + '","' + rec.variables.facility_name + '","' + rec.variables.vendor_no + '","' + rec.variables.vendor_name.getDisplayValue() + '","' + rec.variables.vendor_catalog_no + '","' + rec.variables.unit_of_measure + '","' + rec.variables.conversion_factor + '","' + rec.variables.price_per_uom + '","' + rec.variables.contract_no + '","' + rec.variables.contract_expiration + '","' + rec.variables.manufacturer_no + '","' + rec.variables.manufacturer.getDisplayValue() + '","' + rec.variables.manufacturer_catalog_no + '","","' + rec.variables.national_drug_code + '","FALSE","TRUE"\r\n';

    cnt++;
    // set export state to exported
    rec.setWorkflow(false);
    rec.u_export_state = 'exported';
    rec.update();

    gs.log("csvcontent " + "  " + csvContent);

}

// delete existing attachments
var gr = new GlideRecord('sys_attachment');
gr.addQuery('table_sys_id', '34792a53db28dc10df2f5dd5ce961996');
gr.addQuery('table_name', 'sysevent_email_action');
gr.query();
while (gr.next()) {
    gr.deleteRecord();
}

// update notification attachment contents with csvContent
var now = new GlideDateTime(); // 2020-04-04 14:52:09
//var today = now.getDate(); // 2020-04-04
var fileName = 'ENVI Import from ServiceNow ' + now + '.csv';
var grRec = new GlideRecord("sysevent_email_action");
grRec.addQuery("sys_id", "34792a53db28dc10df2f5dd5ce961996");
grRec.query();
if (grRec.next()) {
    var grAttachment = new GlideSysAttachment();
    grAttachment.write(grRec, fileName, 'application/vnd.ms-excel', csvContent);
}

// fire off event
gs.eventQueueScheduled('SP_Materials_Management_ENVI _import', grRec, '0', '0', 'MM_ENVI_Import_Notification');

[Mark Roethof]

And if you update the field name like I mentioned? What's the result?

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020-2022 ServiceNow Community MVP
2020-2022 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list