Import to MR

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

The simplest method to use is to create a CSV (comma separated values) file with the columns listed below, which must be in the same order. Values can 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 '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.

The way the imports work is it will let you map materials as you import. You should see a list of materials on the right from the CSV and you would fill in the columns on the left for the corresponding material in CN. You can add new Parts/Colors on the fly as you go as well. It will store these mappings and load them the next time you import from that source.

The Element is mainly used for users who are importing budgets from an external estimating system and want to identify specific elements on a project (like each cabinet, etc.). Most customers do not use it.

The Modifier is what you would typically use for those types of custom sizes when ordering. An example for glass, the part would be '1/4" Tempered Glass, Clear, Pencil Polished 4 Edges' and a Modifier of 24" x 32"

We have some functionality where you can name a column in the import source as CNModifier and the data will come right into the Modifier column during the import, however we don't support this currently on the CSV method.

You can import extra columns after the Element column in the CSV. It will be displayed during the import, but won't be considered during the match. You could load the data in, then copy paste it into the Modifier columns for items that needed it.

The sample includes a column like this. You can copy/paste multiple cells at once using the standard windows keyboard shortcuts, so you could load a CNModifier column after the Element column in the CSV with whatever you want to carry over to the Modifier column, then highlight the entire column and copy/paste it into the Modifier column.

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 following code snippet into the query editor window and then click OK

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

Go to Report Center and load the Material Summary (optimized) report, this will populate the Report.mdb file with the necessary data.

Go to Crows Nest (CN) and make a new MR on the matching Project

Click the Import button

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.accdb file.

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.