SOLVED: getting origin through ci_item table

SOLVED

We are using Sage 100 2022.

In my sage account, I have an "origin" column for each item:

(edit: added after MM's reply) You can see the "origin" field, also, in the categories section of an item inquiry:

(end edit)

If that is an in-built sage table column, how do I relate it to the CI_Item table?

Otherwise, through the interface, how can I find out the table and column of the 'Origin' field through the Sage 100 GUI?

This is a current query we are using in an ODBC connection - I'd like to add the origin field there:

SELECT
    CI_Item.ItemCode,
    IM_AliasItem.AliasItemNo, IM_AliasItem.VendorNo,
    CI_Item.LastTotalUnitCost
FROM
    CI_Item, IM_AliasItem
WHERE
    CI_Item.ItemCode = IM_AliasItem.ItemCode
    AND CI_Item.LastTotalUnitCost <> 0

  • 0

    Your Item List looks like a custom view and the "Origin" field is either a User Defined Field (UDF) or a User Defined Table (UDT).  You could try digging around for it under Custom Office.  Depending on how the field is setup, you can try to add CI_Item.UDF_ORIGIN to your SELECT statement and see if it works.

  • 0 in reply to MM_Logic

    Thanks.  This looks like all of my fields; there UDF_origin field is not there:

    odbc> select top 1 * from CI_Item
    ItemCode        ItemType        ItemCodeDesc    ExtendedDescriptionKey  UseInAR UseInSO UseInPO UseInBM CalculateCommission     DropShip        EBMEnabled      PriceCode       PrintReceiptLabels              AllocateLandedCost      WarrantyCode    SalesUnitOfMeasure      PurchaseUnitOfMeasure   StandardUnitOfMeasure   PostToGLByDivision      SalesAcctKey    CostOfGoodsSoldAcctKey  InventoryAcctKey        PurchaseAcctKey ManufacturingCostAcctKey        TaxClass        PurchasesTaxClass       ProductLine     ProductType     Valuation       DefaultWarehouseCode    PrimaryAPDivisionNo     PrimaryVendorNo ImageFile       Category1       Category2       Category3       Category4       ExplodeKitItems ShipWeight      CommentText     RestockingMethod        StandardUnitCost        StandardUnitPrice       CommissionRate  BaseCommAmt     PurchaseUMConvFctr      SalesUMConvFctr Volume  RestockingCharge        ProcurementType DateCreated     TimeCreated     UserCreatedKey  DateUpdated     TimeUpdated     UserUpdatedKey  AllowBackOrders AllowReturns    AllowTradeDiscount      InactiveItem    ConfirmCostIncrInRcptOfGoods    LastSoldDate    LastReceiptDate LastPhysicalCountDate   SalesPromotionCode      SaleStartingDate        SaleEndingDate  SaleMethod      NextLotSerialNo InventoryCycle  RoutingNo       PlannerCode     BuyerCode       LowLevelCode    PlannedByMRP    VendorItemCode  SetupCharge     AttachmentFileName      ItemImageWidthInPixels  ItemImageHeightInPixels LastTotalUnitCost       AverageUnitCost SalesPromotionPrice     SuggestedRetailPrice    SalesPromotionDiscountPercent           TotalQuantityOnHand     AverageBackOrderFillDays        LastAllocatedUnitCost   TotalInventoryValue     CommodityCode   UDF_GTIN        UDF_UPC_CODE    TemplateNo      TrackLotSerialExpirationDates   RequireExpirationDate   CalculateExpDateBasedOn NumberUntilExpiration   CalculateSellByBasedOn  NumberToSellByBefore    NumberToSellByAfter     CalculateUseByBasedOn   NumberToUseByBefore             NumberToUseByAfter      CalculateReturnsBasedOn NumberToReturnAfter

  • 0 in reply to MM_Logic

    Yes, a UDF is certainly a possibility, but it could also be one of the Category# fields (which are named in IM Options).

  • +1 in reply to Daniel Ahern
    verified answer

    In that case, look at Item Inquiry and look at the Categories like Kevin has mentioned.  Top is 1 and the bottom is 4 unless that screen is moved around.  Your other option would be to add Category1, Category2, Category3, and Category4 to your query.

    Edit: In your case, it looks to be Category2

  • +1 in reply to Kevin M
    verified answer

    This field should be CI_Item.Category2