Page Comparison

Material Requisition (MR) line items can be populated by importing from an Import Source.

The simplest method to use is to create a CSV list (comma separated values) file with the columns listed below in that , which must be in the same order. You Values can leave values blank in the columns. You can download a sample here to start with.be left blank except for the Qty.

Columns: Qty, Part, Color1, Color2, Modifier, Element

...

To create the Import Source, go to an MR and click the Import button. Then right-click on the list of Import Sources and Add a new one and name it accordingly, like 'Adesk Cabinetvision CSV' or something.

Once it's been added, right-click the new source and Change Type to CSV (with or without a header row, the sample has a header). Then you can select the source and click OK to start an import.

...

How to import from Cabinet Vision

...

Create the import

Open Crows Nest (CN) and go to any existing MR on any Project
Click the Import button on the MR
In the ‘Select a Source’ window, right-click then click on or hover over ‘Add’ to open the flyout textbox on the right, enter a name for the import like ‘CV’ and then press Enter
Right-click the new source and ‘Change Type to…’ then ‘MS Access Database File’
Then right click the new source and click ‘Edit Query’
Paste the contents of the file below (MR Import from CabinetVision.txt) following code snippet into the query editor window and then click OK

...

Code Block

language	none

SELECT Round(Switch(
      CxMaterial.UnitOfIssueID = 1, Fix(Count([Part ID])+(Count([Part ID])*(Nz([MaterialOverride].[Waste], 0)/100))+0.999999999)
      , CxMaterial.UnitOfIssueID = 2, Sum([Parts].[Length]/12+(([Parts].[Length]/12)*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 3, Sum([Area]/144+(([Area]/144)*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 4, Sum((([Area]*([CxExtraBoardInfo].[RoughThickness]/25.4))/144)+((([Area]*([CxExtraBoardInfo].[RoughThickness]/25.4))/144)*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 5, Sum((([Area]/144)/(IIf(((([CxExtraSizeInfo].[Width])>0) And (([CxExtraSizeInfo].[Length])>0)),(([CxExtraSizeInfo].[Width]/25.4)*([CxExtraSizeInfo].[Length]/25.4))/144,1)))+((([Area]/144)/(IIf(((([CxExtraSizeInfo].[Width])>0) And (([CxExtraSizeInfo].[Length])>0)),(([CxExtraSizeInfo].[Width]/25.4)*([CxExtraSizeInfo].[Length]/25.4))/144,1)))*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 6, Sum([Parts].[Length]*0.0254+(([Parts].[Length]*0.0254)*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 7, Sum(([Area]*0.0254*0.0254)+(([Area]*0.0254*0.0254)*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 8, Sum((([parts].[Area]*0.0254*0.0254)*(([CxExtraBoardInfo].[RoughThickness]/25.4)*0.0254))+((([parts].[Area]*0.0254*0.0254)*(([CxExtraBoardInfo].[RoughThickness]/25.4)*0.0254))*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 9, Sum((([parts].[Area]*0.0254*0.0254)*(([CxExtraBoardInfo].[RoughThickness]/25.4)*0.0254))+((([parts].[Area]*0.0254*0.0254)*(([CxExtraBoardInfo].[RoughThickness]/25.4)*0.0254))*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 10, Sum((([Parts].[Width]/12)*([Parts].[Length]/12)*([CxExtraSizeInfo].[Thickness]/25.4))+((([Parts].[Width]/12)*([Parts].[Length]/12)*([CxExtraSizeInfo].[Thickness]/25.4))*(Nz([MaterialOverride].[Waste], 0)/100)))
      , CxMaterial.UnitOfIssueID = 11, Fix(Count([Part ID])+(Count([Part ID])*(Nz([MaterialOverride].[Waste], 0)/100))+0.999999999)
      , 1, CStr(CxMaterial.UnitOfIssueID)
      ), 2) AS Qty
  , CxMaterial.Name AS Part
  , NULL AS Color1
, Iif(
      InStr(LCase(CxMaterial.Name),"plam")>0
      OR InStr(LCase(CxMaterial.Name),"PL")>0
      OR InStr(LCase(CxMaterial.Name),"p lam")>0
      OR InStr(LCase(CxMaterial.Name),"solid surface")>0
      , [Job Info].[Job Number]
      , NULL) AS Color2
  , NULL AS Modifier, NULL AS Element
, Switch(
      CxMaterial.UnitOfIssueID = 1, 'Each'
      , CxMaterial.UnitOfIssueID = 2, 'Ft'
      , CxMaterial.UnitOfIssueID = 3, 'SqFt'
      , CxMaterial.UnitOfIssueID = 4, 'BdFt'
      , CxMaterial.UnitOfIssueID = 5, 'Sheet'
      , CxMaterial.UnitOfIssueID = 6, 'M'
      , CxMaterial.UnitOfIssueID = 7, 'SqM'
      , CxMaterial.UnitOfIssueID = 8, 'BdM'
      , CxMaterial.UnitOfIssueID = 9, 'CubM'
      , CxMaterial.UnitOfIssueID = 10, 'CubFt'
      , CxMaterial.UnitOfIssueID = 11, 'Pair'
      , 1, CStr(CxMaterial.UnitOfIssueID)
      ) AS UOM
FROM (((((((
  Parts
  LEFT JOIN CxMaterial ON Parts.[Material ID] = CxMaterial.ID)
  LEFT JOIN CxExtraBoardInfo ON CxMaterial.ID = CxExtraBoardInfo.MaterialID)
  LEFT JOIN CxExtraSizeInfo ON CxMaterial.ID = CxExtraSizeInfo.MaterialID)
  LEFT JOIN MaterialOverride ON CxMaterial.ID = MaterialOverride.MatID)
  INNER JOIN Cabinets ON [Parts].[Cabinet ID] = [Cabinets].[Cabinet ID])
  INNER JOIN Rooms ON Cabinets.[Room ID] = Rooms.[RoomNumber])
  INNER JOIN [Job Info] ON Rooms.[Job ID] = [Job Info].ID)
WHERE Parts.BuyOut = 0
GROUP BY CxMaterial.Name, CxMaterial.DefaultCost, Nz([MaterialOverride].[Waste], 0)/100, [CxExtraBoardInfo].[RoughThickness]/25.4, [CxExtraSizeInfo].[Thickness]/25.4, CxMaterial.UnitOfIssueID, IIf(((([CxExtraSizeInfo].[Width])>0) And (([CxExtraSizeInfo].[Length])>0)),(([CxExtraSizeInfo].[Width]/25.4)*([CxExtraSizeInfo].[Length]/25.4))/144,1)
, Iif(
      InStr(LCase(CxMaterial.Name),"plam")>0
      OR InStr(LCase(CxMaterial.Name),"PL")>0
      OR InStr(LCase(CxMaterial.Name),"p lam")>0
      OR InStr(LCase(CxMaterial.Name),"solid surface")>0
      , [Job Info].[Job Number]
      , NULL)
UNION ALL 
SELECT Count(Molding.[Molding ID]) AS Qty
  , CxMaterial.Name AS Part
  , NULL AS Color1
  , NULL AS Color2
  , NULL AS Modifier
  , NULL AS Element
  , 'Molding'
FROM Molding
  LEFT JOIN CxMaterial ON Molding.[Material ID] = CxMaterial.ID
WHERE Molding.[Std Length] > 0
GROUP BY CxMaterial.Name
  , CxMaterial.Waste
ORDER BY UOM, Part
;

...

Use the Import

Open Cabinet Vision (CV) and open the job you want to import

...

Select the CV source in the list and click OK. The first time you do this, you’ll need to browse to the location of your Report.mdb accdb file.

Code Block
C:\Cabinet Vision\Users\{name of account here}

You may need help to find the right one. The next time you import, you can right-click the CV source in the list and uncheck the ‘Prompt for File’ option and you won’t have to select the file each time.

This should open the ‘Import MR Items’ window with the data loaded from the CV data file so you can map the materials from CV to CN and populate the MR.

Using Cabinet Vision 2024+

The 2024 version of CV switched from an Access file to a SQL LocalDB MDF file. The steps are mostly the same with the following changes.

The import type should be changed to: ODBC

A Connection String needs to be added:

Code Block
Provider=SQLNCLI11;Server=(localdb)\cv24;Database=CV-@Filename;Integrated Security=SSPI;

The query needs to be converted from Access SQL to SQL Server syntax, following is a sample

Code Block

language	sql

SELECT
    ROUND(
        CASE
            WHEN CxMaterial.UnitOfIssueID = 1 THEN FLOOR(COUNT([Part ID]) + (COUNT([Part ID]) * (COALESCE([MaterialOverride].[Waste], 0) / 100)) + 0.999999999)
            WHEN CxMaterial.UnitOfIssueID = 2 THEN SUM([Parts].[Length] / 12 + ([Parts].[Length] / 12) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 3 THEN SUM([Area] / 144 + ([Area] / 144) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 4 THEN SUM((([Area] * ([CxExtraBoardInfo].[RoughThickness] / 25.4)) / 144) + (([Area] * ([CxExtraBoardInfo].[RoughThickness] / 25.4)) / 144) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 5 THEN SUM((([Area] / 144) / (CASE WHEN ([CxExtraSizeInfo].[Width] > 0 AND [CxExtraSizeInfo].[Length] > 0) THEN ([CxExtraSizeInfo].[Width] / 25.4) * ([CxExtraSizeInfo].[Length] / 25.4) / 144 ELSE 1 END)) + (([Area] / 144) / (CASE WHEN ([CxExtraSizeInfo].[Width] > 0 AND [CxExtraSizeInfo].[Length] > 0) THEN ([CxExtraSizeInfo].[Width] / 25.4) * ([CxExtraSizeInfo].[Length] / 25.4) / 144 ELSE 1 END)) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 6 THEN SUM([Parts].[Length] * 0.0254 + ([Parts].[Length] * 0.0254) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 7 THEN SUM(([Area] * 0.0254 * 0.0254) + ([Area] * 0.0254 * 0.0254) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 8 THEN SUM((([parts].[Area] * 0.0254 * 0.0254) * (([CxExtraBoardInfo].[RoughThickness] / 25.4) * 0.0254)) + (([parts].[Area] * 0.0254 * 0.0254) * (([CxExtraBoardInfo].[RoughThickness] / 25.4) * 0.0254)) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 9 THEN SUM((([parts].[Area] * 0.0254 * 0.0254) * (([CxExtraBoardInfo].[RoughThickness] / 25.4) * 0.0254)) + (([parts].[Area] * 0.0254 * 0.0254) * (([CxExtraBoardInfo].[RoughThickness] / 25.4) * 0.0254)) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 10 THEN SUM((([Parts].[Width] / 12) * ([Parts].[Length] / 12) * ([CxExtraSizeInfo].[Thickness] / 25.4)) + (([Parts].[Width] / 12) * ([Parts].[Length] / 12) * ([CxExtraSizeInfo].[Thickness] / 25.4)) * (COALESCE([MaterialOverride].[Waste], 0) / 100))
            WHEN CxMaterial.UnitOfIssueID = 11 THEN FLOOR(COUNT([Part ID]) + (COUNT([Part ID]) * (COALESCE([MaterialOverride].[Waste], 0) / 100)) + 0.999999999)
            ELSE CAST(CxMaterial.UnitOfIssueID AS VARCHAR)
        END, 2) AS Qty,
    CxMaterial.Name AS Part,
    NULL AS Color1,
    CASE
        WHEN CHARINDEX('plam', LOWER(CxMaterial.Name)) > 0
             OR CHARINDEX('pl', LOWER(CxMaterial.Name)) > 0
             OR CHARINDEX('p lam', LOWER(CxMaterial.Name)) > 0
             OR CHARINDEX('solid surface', LOWER(CxMaterial.Name)) > 0
        THEN [Job Info].[Job Number]
        ELSE NULL
    END AS Color2,
    NULL AS Modifier, NULL AS Element,
    CASE
        WHEN CxMaterial.UnitOfIssueID = 1 THEN 'Each'
        WHEN CxMaterial.UnitOfIssueID = 2 THEN 'Ft'
        WHEN CxMaterial.UnitOfIssueID = 3 THEN 'SqFt'
        WHEN CxMaterial.UnitOfIssueID = 4 THEN 'BdFt'
        WHEN CxMaterial.UnitOfIssueID = 5 THEN 'Sheet'
        WHEN CxMaterial.UnitOfIssueID = 6 THEN 'M'
        WHEN CxMaterial.UnitOfIssueID = 7 THEN 'SqM'
        WHEN CxMaterial.UnitOfIssueID = 8 THEN 'BdM'
        WHEN CxMaterial.UnitOfIssueID = 9 THEN 'CubM'
        WHEN CxMaterial.UnitOfIssueID = 10 THEN 'CubFt'
        WHEN CxMaterial.UnitOfIssueID = 11 THEN 'Pair'
        ELSE CAST(CxMaterial.UnitOfIssueID AS VARCHAR)
    END AS UOM
FROM Parts
LEFT JOIN CxMaterial ON Parts.[Material ID] = CxMaterial.ID
LEFT JOIN CxExtraBoardInfo ON CxMaterial.ID = CxExtraBoardInfo.MaterialID
LEFT JOIN CxExtraSizeInfo ON CxMaterial.ID = CxExtraSizeInfo.MaterialID
LEFT JOIN MaterialOverride ON CxMaterial.ID = MaterialOverride.MatID
INNER JOIN Cabinets ON Parts.[Cabinet ID] = Cabinets.[Cabinet ID]
INNER JOIN Rooms ON Cabinets.[Room ID] = Rooms.[RoomNumber]
INNER JOIN [Job Info] ON Rooms.[Job ID] = [Job Info].ID
WHERE Parts.BuyOut = 0
GROUP BY CxMaterial.Name, CxMaterial.DefaultCost, COALESCE([MaterialOverride].[Waste], 0) / 100, [CxExtraBoardInfo].[RoughThickness] / 25.4, [CxExtraSizeInfo].[Thickness] / 25.4, CxMaterial.UnitOfIssueID,
         CASE WHEN ([CxExtraSizeInfo].[Width] > 0 AND [CxExtraSizeInfo].[Length] > 0) THEN ([CxExtraSizeInfo].[Width] / 25.4) * ([CxExtraSizeInfo].[Length] / 25.4) / 144 ELSE 1 END,
         CASE
            WHEN CHARINDEX('plam', LOWER(CxMaterial.Name)) > 0
                 OR CHARINDEX('pl', LOWER(CxMaterial.Name)) > 0
                 OR CHARINDEX('p lam', LOWER(CxMaterial.Name)) > 0
                 OR CHARINDEX('solid surface', LOWER(CxMaterial.Name)) > 0
            THEN [Job Info].[Job Number]
            ELSE NULL
        END
UNION ALL
SELECT
    COUNT(Molding.[Molding ID]) AS Qty,
    CxMaterial.Name AS Part,
    NULL AS Color1, NULL AS Color2, NULL AS Modifier, NULL AS Element, 'Molding' AS UOM
FROM Molding
LEFT JOIN CxMaterial ON Molding.[Material ID] = CxMaterial.ID
WHERE Molding.[Std Length] > 0
GROUP BY CxMaterial.Name, CxMaterial.Waste
ORDER BY UOM, Part;

Versions Compared

Old Version 4

New Version Current

Key

How to import from Cabinet Vision

Create the import

Use the Import

Using Cabinet Vision 2024+