Setup
- Download and open the newest AP data from the SharePoint. (Sung puts it in there every 15th and 1st) (Don't open it online, download it)
- Save the CSV as a XLSX file with the name format "MO-DA-YR AP Data" to the WorkSpace Sharepoint under {Operations>Payments>VendorPay AP data>AirTable Imports}.
- Create a new tab named "Properties", one named "VxP" and another one named "Vendors". Rename the first tab with all the CSV Raw data to "Raw".
Properties Tab
- Copy and paste all data on the Raw tab to the Properties tab. Select columns A,B,C,D,E,F,G,H,N,P,Q,R and delete them.
- Select all fields and choose Remove Duplicates option under the Data tab.
- Select the 'my list has headers' option and then use the select all button to make sure that all option are UNSELECTED. Then select only the Property Accounting Company Title option. This should generate a list of properties that is close in volume to the existing property count in AirTable. If you have a vastly different number, stop and ask for help.
- Insert 3 new columns between the Property Accounting Company Company Title column and the Property VendorPay Status column.
- The new column B should be named "Total T12 $AP", the new column C should be named "VendorPay T12 $AP" and the new column D should be named "T12 Invoices".
- Under the Total T12 $AP column, the first available row (row 2) should be filled with the below formula. Copy the formula to all the way down to all properties.
- =SUMIFS(Raw!A:A,Raw!I:I,Properties!A2)
- Under the VendorPay T12 $AP column, the first available row (row 2) should be filled with the below formula. Copy the formula all the way down to all properties.
- =SUMIFS(Raw!A:A,Raw!I:I,Properties!A2,Raw!H:H,"TRUE")
- Under the T12 Invoices column, the first available row (row 2) should be filled with the below formula. Copy the formula all the way down to all properties.
- =COUNTIFS(Raw!I:I,Properties!A2)
- Select Column B and C and using the home tab, format both columns as financial/currency/accounting columns.
- Save everything.
VxP Tab
- Copy and paste all data on the Raw tab to the VxP tab. Select columns A,B,C,D,G,K,P,Q,R and delete them.
- Select column H (Matching Flag d' Alex) and rename it to "Matching Code".
- Select all fields and choose Remove Duplicates option under the Data tab.
- Select the 'my list has headers' option and then use the select all button to make sure that all options are UNSELECTED. The select only the Property Accounting Company Name and Matching Code options.
- Save everything.
Vendors Tab
- Copy and paste all data on the Raw tab to the Vendors tabs. Select columns A,B,C,D,I,J,K,O,P,Q,R and delete them.
- Select column G (Matching Flag d' Alex) and rename it to Matching Code.
- Select all fields and choose Remove Duplicates option under the Data tab.
- Select the 'my list has headers' option and then use the select all button to make sure that all options are UNSELECTED. Then select only the Matching Code option. This should generate a list of vendors that is close in volume to the existing vendor count in AirTable. If you have a vastly different number, stop and ask for help.
- Insert 3 new columns between Vendor Name column and the Vendor Code column.
- The new column B should be named "Total T12 $AP", the new column C should be named "# of T12 Invoices", and the new column D should be named "# of Properties".
- Under row 2 of column B, enter the below formula and copy it to all rows.
- =SUMIFS(Raw!A:A,Raw!N:N,Vendors!J2)
- Under row 2 of column C, enter the below formula and copy it to all rows.
- =COUNTIFS(Raw!N:N,Vendors!J2)
- Under row 2 of column D, enter the below formula and copy it to all rows.
- =COUNTIFS(VxP!H:H,Vendors!J2)
- Select column B and using the home tab, format the column as financial/currency/accounting.
- Save everything.
Import Prep
- Select the properties tab. Then press the Save As or Save a Copy button.
- Select an easy to remember location, like desktop or downloads. Name the document as "MO-DA-YR Property Data". Select the file format to be CSV. Press Save.
- You may get a popup that tells you that the selected file format cannot save multiple sheets and will ask if you want to continue. Select Yes or Ok (Only the properties tab will save to the CSV file because you selected it in step 1).
- Save and close the file. Reopen the original MO-DA-YR AP Data.XLSX file that you saved in the WorkSpace sharepoint.
- Select the vendors tab. Then press the Save As or Save a Copy button.
- Select an easy to remember location, like desktop or downloads. Name the document as "MO-DA-YR Vendor Data". Select the file format to be CSV. Press Save.
- You may get a popup that tells you that the selected file format cannot save multiple sheets and will ask if you want to continue. Select Yes or Ok (Only the vendors tab will save to the CSV file because you selected it in step 3).
- Save and close the file.
Import - Very sensitive
- Open AirTable, navigate to the VendorPay Master Data base and select the Properties tab.
- BEFORE DOING ANYTHING ELSE - In the top of the page, select the clock with an arrow going around it. This will open the "history" tab. Select the Snapshots option. Press the "take a snapshot" option. This saves a backup of the base in case anything goes wrong.
- Select extensions and click on the CSV import button.
- You'll be asked to select a file. Choose the Properties data CSV file that you created and saved to a place like your desktop or downloads folder.
- For the Table dropdown, select Properties.
- Under the setting section, turn on the merge with existing records option. Under the merge field dropdown, choose Property. Leave off the Skip blank or invalid CSV values. Turn on the First row of CSV file is headers option. Turn on the create missing select options option.
- Under the field mappings section, Make sure the following items are turned on and mapped as listed below. If an item is not listed below, do not turn it on or map it.
- Property -> Property Accounting Company Title
- Property VendorPay Status -> Property VendorPay Status
- Property Accounting Company Status -> Property Accounting Company Status
- Division -> Division
- Accounting System Name -> Accounting System Name
- Total T12 $AP -> Total T12 $AP
- VendorPay T12 $AP -> VendorPay T12 $AP
- Press the save button.
- You might get an error message saying some values won't be imported. As long as the values are null values or are values like $- then you can select the continue button. If you are unsure, ask for help.
- Select the Vendors tab and open the extensions section again. Select the CSV import option and choose the Vendors data CSV file that you saved earlier.
- For the table dropdown, select Vendors.
- Under the setting section, turn on the merge with existing records option. Under the merge field dropdown, choose Unique ID. Leave off the Skip blank or invalid CSV values. Turn on the First row of CSV file is headers option. Turn on the create missing select options option.
- Under the field mappings section, Make sure the following items are turned on and mapped as listed below. If an item is not listed below, do not turn it on or map it.
- Unique ID -> Unique ID
- Vendor Name -> Vendor Name
- Total T12 $AP -> Total T12 $AP
- FEIN -> FEIN
- Vendor Active on VP -> Vendor Active on VP
- Property Accounting Company Title -> Property Accounting Company Title
- Property VendorPay Status -> Property VendorPay Status
- Property Accounting Company Status -> Property Accounting Company Status
- Division -> Division
- Accounting System Name -> Accounting System Name
- Vendor Code -> Vendor Code
- Matching Flag d'Alex -> Matching Flag d'Alex
- Press the save button.
- You might get an error message saying some values won't be imported. As long as the values are null values or are values like $- then you can select the continue button. If you are unsure, ask for help.