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