{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Cover Page","Frame File","DropDown Lists","Financial Groups","Version Notes"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":10,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover Page","index":0,"n_rows":18,"n_cols":17,"rows_kept":18,"cut":false,"hidden":false,"merged":[[17,3,23,19],[16,3,16,19],[2,4,13,18]]}
[]
[]
[null,null,null,null,"Master Frame Load File - v2.0"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Modified Date - 03/26/2020"]
[]
[]
[null,null,null,"Instructions:\r\n1.  You must enable Macros to use validation.\r\n2. *First Time Upload* - Please paste frame data into the 'Frame File' tab starting on cell A12. Right click and use \"Paste Values\" in order to retain validation formatting.\r\n3. The Frame File tab has formatting to help easily display when data is missing. If a cell within the pasted data is showing a light red color, required data is missing. If a light blue fill appears in your data, a \"zero\" measurement needs to be corrected. If a light yellow color appears, this is missing optional data, data missing in those columns is not required for upload.\r\n4. Row 8 contains a data check field that will compare the total number of entries in Column A against all other columns in the tab. If all columns show a green 'valid' text, the column is not missing data in a cell in that column. If 'missing data' appears in red, please check the column where the the missing data cell is shown and look for light red cells."]

{"sheet":"Frame File","index":1,"n_rows":12,"n_cols":35,"rows_kept":12,"cut":false,"hidden":false,"merged":[[0,8,1,8],[5,8,6,8],[5,9,6,9]]}
[1,1,1,1,1,1,1,1,"Data Check",1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
[1,1,1,1,1,1,1,1,null,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
[1,1,1,1,1,1,1,null,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
[1,1,1,1,1,1,1,1,"Total Empty cells:",21,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
[1,1,1,1,1,1,1,1,"Total Number of Frames Submitted",1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
[1,1,1,1,1,1,1,1,"Number of cells that contain a \"0\" value:",0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
[1,1,1,1,1,1,1,1,null,null,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,null,null,null,null,1]
["Data Check:",{"$f":"IF($A$9=B9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=C9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=D9, \"Valid\", \"Missing Data\")","v":"Missing Data"},"Missing Data","Missing Data","Missing Data","Missing Data","Missing Data",{"$f":"IF($A$9=J9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=K9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=L9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=M9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=N9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=O9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=P9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=Q9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=R9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=S9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=T9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=U9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=V9, \"Valid\", \"Missing Data\")","v":"Missing Data"},{"$f":"IF($A$9=W9, \"Valid\", \"Optional Data Missing\")","v":"Optional Data Missing"},{"$f":"IF($A$9=X9, \"Valid\", \"Optional Data Missing\")","v":"Optional Data Missing"},"Optional Data Missing","Optional Data Missing","Optional Data Missing","Optional Data Missing","Optional Data Missing","Optional Data Missing","Optional Data Missing",{"$f":"IF($A$9=AF9, \"Valid\", \"Optional Data Missing\")","v":"Optional Data Missing"},{"$f":"IF($A$9=AG9, \"Valid\", \"Optional Data Missing\")","v":"Optional Data Missing"},{"$f":"IF($A$9=AH9, \"Valid\", \"Optional Data Missing\")","v":"Optional Data Missing"},{"$f":"IF($A$9=AI9, \"Valid\", \"Missing Data\")","v":"Missing Data"}]
[{"$f":"COUNTIF(A12:A200000,\"*?\")","v":1},{"$f":"COUNTIF(B12:B200000,\"*?\")","v":0},{"$f":"COUNTIF(C12:C200000,\">0\")","v":0},{"$f":"COUNTIF(D12:D200000,\">0\")","v":0},0,0,0,0,0,0,0,{"$f":"COUNTIF(L12:L200000,\">0\")","v":0},{"$f":"COUNTIF(M12:M200000,\"*?\")","v":0},0,0,{"$f":"COUNTIF(P12:P200000,\">0\")","v":0},0,0,0,0,0,{"$f":"COUNTIF(V12:V200000,\">0\")","v":0},{"$f":"COUNTIF(W12:W200000,\">0\")","v":0},0,0,0,0,0,0,0,0,{"$f":"COUNTIF(AF12:AF200000,\"*?\")","v":0},0,0,{"$f":"COUNTIF(AI12:AI200000,\"*?\")","v":0}]
["Style Name / Description","Frame Color","Eye Measurement","Temple","A Measurement","B Measurement","ED Measurement","DBL Measurement","Bridge","Default Cost","Wholesale Cost","UPC #","Style Category","Item Status","Medicaid","Item Number","Vendor","COLLECTION","MODEL","Frame Edge Type   ","FRAME TYPE","Default Retail Price","Custom Retail Price 1","Custom Retail Price 2","Custom Retail Price 3","Custom Retail Price 4","Gender Type","Age Group","Frame Class","Financial Group Name","Company ID","Supply Source","Item Group","Generic Frame","Frame Part"]
["EX:   CK114, BEBE Charmed \n\nData Type: CHAR","Can be a word, a code, etc.  Examples include:  TT Silver, BLK, Crystal Blue Fade, 058\n\nData Type: CHAR","Data Type: INT","Data Type: INT","Data Type: INT","Data Type: INT","Data Type: INT","Data Type: INT","Data Type: INT","Your purchase cost\n\nData Type: DEC (5,2)","Will display as wholesale cost at the item level and wholesale price at the style level.  \n\nThis value can be used for some insurance calcs, as well as in bulk pricing\n\nData Type: DEC (5,2)","The Universal Product Code for this Frame (Barcode #)\n\nData Type: INT","Sunglass\n Safety\n or \nOphthalmic \n\nThese are the only options supported\n\nData Type: CHAR","Examples: \nActive\nRecall\nDiscontinued\n\nProvide the status or status codes you wish to use.   Please provide one word description for status.\n\nData Type: CHAR","If Medicaid, enter 'Y', if not provide 'N'\n\nData Type: CHAR","Must be unique - can be a SKU number, can also be the UPC number\n\nData Type: CHAR","Vendor Name  - associated with the Collection\n\nVendors must be created by client.\n\nThis load process will synch the Vendor to the Frame Collection.\n\nData Type: CHAR","Frame Collection\n\nData Type: CHAR","Used to create item name.  Can be the same as the description or an abbreviation of the description.   Example:   CK5963 can be the style and the model.   Stylewise 310 might be the description but SW310 could be the model.  Item name is also shown on frame tags.\n\nData Type: CHAR","Displays in Frame Style.   \nPick from drop down list. \nPossible values are: 3 Piece Mount, Beveled, Full Rim Metal, Full Rim, Zyl, Grooved Rimless, Half Eye, Metal, Half Eye Zyl, Metal, Nylor, Metal, Nylor Zyl, Rimless Metal, Rimless Zyl, Shield, Standard Zyl, Sport Wrap\n\nData Type: CHAR","Frame Material\n\nSelect from: \n\nDrill Mount\nMetal\nZyl\nNylor Metal\nFacet\nRimless\n\nData Type: CHAR\n\n\n\n","This is the default price list.\n\nData Type: DEC (5,2)","Change the name of the row above to identify a pricelist name for this pricing set that is configured in the AcuityLogic Admin. Make sure that your price list name contains the word \"Retail\" in it.\n\nData Type: DEC (5,2)","Change the name of the row above to identify a pricelist name for this pricing set that is configured in the AcuityLogic Admin. Make sure that your price list name contains the word \"Retail\" in it.\n\nData Type: DEC (5,2)","Change the name of the row above to identify a pricelist name for this pricing set that is configured in the AcuityLogic Admin. Make sure that your price list name contains the word \"Retail\" in it.\n\nData Type: DEC (5,2)","Change the name of the row above to identify a pricelist name for this pricing set that is configured in the AcuityLogic Admin. Make sure that your price list name contains the word \"Retail\" in it.\n\nData Type: DEC (5,2)","Optional Field.  Some inventory reports provide the ability to include Gender Type.\n\nExamples:\nFemale\nMale\nUnisex\n\nData Type: CHAR","Optional Field.  Some inventory reports provide the ability to include Age Group.\nExamples:\nAdult\nChildren\n\nData Type: CHAR","Optional Field. Some inventory reports provide the ability to include Frame Class.  \n\nData Type: CHAR","Financial groups are used to create groupings of similar product for GL reporting.  Examples could include 'Consignment', 'Private Label' but can be used as you wish.\n\nEnter your Master List of Frame Financial Groups on the Financial Groups tab of this document.\n\nData Type: CHAR","Three Options:\r\n1) If the retail price is shared, leave this field blank and put retail pricing in column V, default retail price\r\n2)If the retail price is not shared and frame is active in parent & child company list all companies in this field. Default retail price (column V) is parent company & child company retial price is custom price # (column w - z) \r\n3) If the frame is only active in one company, enter company name in this field and retial price goes in default retial price field (column V)\n\nData Type: CHAR","Select From:\n\nDC\n DC if Available\n Vendor\n\nDC: If a frame is sold, a replenishment is created and added to the pick list for the distribution center.\n\nDC if Available: If inventory reflects the quantity on hand, the frame is added to the pick list from the distribution center. If you have none on hand at the distribution center, then it is added to a vendor pick list.\n\nVendor: Anything from the distribution center is ordered from the vendor.\n\nData Type: CHAR","Value = Standard Frame or any existing valid group or Empty\n\nData Type: CHAR","Value = 1 for Generic Frame\nelse 0 or empty\n\nData Type: BIT","Default to:\nComplete\n\nData Type: CHAR"]
["Paste data here using \"Paste Values\""]

{"sheet":"DropDown Lists","index":2,"n_rows":27,"n_cols":7,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["ID","Type","Value","Ordinal","IsDefaultValue","ALKey","ALPublishedDate"]
[118,16,"Drill Mount",1,0,"al.16.118",41710.736805555556]
[119,16,"Zyl",2,0,"al.16.119",41710.736805555556]
[120,16,"Nylor Metal",3,0,"al.16.120",41710.736805555556]
[121,16,"Metal",4,0,"al.16.121",41710.736805555556]
[122,16,"Facet",5,0,"al.16.122",41710.736805555556]
[123,16,"Rimless",6,0,"al.16.123",41710.736805555556]
[]
[]
[]
[]
["Frame Style List",null,"Character Limit",null,"Gender Types",null,"Supply Source"]
["3 Piece Mount",null,100,null,"Male",null,"DC"]
["Beveled",null,50,null,"Female",null,"Vendor"]
["FULL RIM METAL",null,null,null,"Unisex",null,"DC if Available"]
["FULL RIM ZYL"]
["Grooved Rimless"]
["HALF EYE METAL"]
["HALF EYE ZYL"]
["METAL"]
["NYLOR METAL"]
["NYLOR ZYL"]
["RIMLESS METAL"]
["RIMLESS ZYL"]
["SHIELD"]
["STANDARD ZYL"]
["SPORT WRAP"]

{"sheet":"Financial Groups","index":3,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["Financial Group Name"]

{"sheet":"Version Notes","index":4,"n_rows":42,"n_cols":2,"rows_kept":42,"cut":false,"hidden":false,"merged":[[14,1,15,16],[16,1,17,16],[18,1,19,16]]}
["Change Log:"]
[null,"Ver 2.1"]
[null,"Added data validation check in VBA to check columns C:I, T, U for data validation requirements, report back if failed via msgbox to client."]
[null,"Ver 2.0"]
[null,"Added conditional formatting for \"0\" cell checks. Easier to visualize when a measurement does not have a valid value."]
[null,"Adding loop for \"0\" cell check to give a msgbox alert. However, due to the volume of alerts, disabling this code to lower overall total messages. "]
[null,"Added \"0\" check in the VBA code to give a count of the total number of cells with zero in them."]
[null,"Ver 1.9"]
[null,"Data validation formatting used to check against drop down entries in columns \"T\" and \"U\". If the data is not valid, msgbox pops up."]
[null,"Moved VBA code for UL and UR as well as making C (array range) public as a variable. "]
[null,"Ver 1.8"]
[null,"Button added for data validation and frame check. Looks for blank cells from user."]
[null,"Removed % missing data. Not useful for assessment. "]
[null,"Ver 1.7"]
[null,"ENTER FILE INTO BETA STAGE: Range of user data is now completely dynamic based off end-column and first pasted row setpoints. Multiple user messages based off \"completeness\" of data pasted into sheet."]
[]
[null,"TO BE COMPLETED: Fix % display for missing data: move down to a whole number. Fix msgbox display of missing cells to format in columns of data to keep data from spilling off bottom of page. Add button to trigger macro for ease of testing. "]
[]
[null,"Notes: Optional data columns moved to end of dataset. More clearly seperates what we \"need\" to the client/user. Updated \"Frame File\" heads-up note to client: Optional data is not checked by data check field."]
[]
[null,"Ver 1.6"]
[null,"User is shown less than 10 empty cells as a default. Any empty cell count in the range greater than 10 will result in a user interface for input by client."]
[null,"Ver 1.5"]
[null,"Shows the user the immedate result of the number of missing cells and the 1st row missing data in the \"Frame file\" tab."]
[null,"Function returns the appropriate number of blank cells when the requested value >= blanks."]
[null,"Ver 1.4"]
[null,"Set test range for array to A12:AD24. This will allow for a subroutine check for validity of process and usability for client."]
[null,"Added live data check formulas in upper right hand corner of \"Frame File\" tab. Will serve as a countdown for parsing out missing data after client pastes."]
[null,"Ver 1.3"]
[null,"Formatted the cover page to adhere to the Eyefinity template standard. Changed colors to align, fonts, and general sizing."]
[null,"Locked scroll area of \"Frame File\" tab to A:AD."]
[null,"Inserted dropdown lists back to original state, allowing for selection of frame edge type, etc. "]
[null,"Ver 1.2"]
[null,"Added message box to display names of open cells and total number. Later to run via user interface button."]
[null,"Macro to find the first empty cell in Column A. Later to be refined to automatically find appropriate array for data in file."]
[null,"Locked scroll area of cover page to A1:W34 "]
[null,"Ver 1.1"]
[null,"Added row 8 data check as an intermediary data validation for missing data. Needs refinement via VBA, but will work to find missing columns of data."]
[null,"Ver 1.0"]
[null,"Added drop down options for column J to row 15 (missing previously)"]
[null,"Added conditional formatting based on required and optional data fields to visually highlight missing data when scrolling: red is missing required data, yellow is optional data"]
[null,"Expanded row 13 height to 300 to allow for all text to be visible"]