In this walkthrough, you will learn how to create PILOT companies on which you can test your data, and modify and delete data as required, and then create a LIVE company. 

Important: In order to delete database data from the Clean Company package, you need a developer license. A customer license does NOT have permission to delete data from all of the tables created in the Clean Company package.

About This Walkthrough

This walkthrough provides an overview on how to do the following:

Story

Louise's Bakery is implementing JustFoodERP, and wants to test the product before going live. The JustFoodERP team has already created the LouiseBakeryPILOT_1 company, which contains all of the necessary setups and data.

Louise's Bakery has worked with the initial LouiseBakeryPILOT_1 company, but now wants to make some changes to the setup. Because of existing transactions, some of the setup cannot be changed, so the LouiseBakeryPILOT_1 company was copied over to the LouiseBakeryPILOT_2 company, and the transactions were deleted from LouiseBakery_PILOT2. This company with no transaction data is known as a clean company.

After working with the data, Louise's Bakery discovered that the YOGIES items were incorrectly added to the database, and all of the YOGIES items need to be deleted. By creating a new configuration package that contained the Item table, and copying the database data to the package, the YOGIES items were deleted from the package, and then the package was applied to the database, which deleted the YOGIES items.

Louise's Bakery also wanted the customer address to have a consistent format. The first step, in making addresses consistent, was to change all references of "PO boxes" to "P.O. Boxes". This was done using an Excel spreadsheet, which allows you to perform a search and replace. While using the Excel spreadsheet, Louise's Bakery also discovered an incorrect record, which was then marked for deletion. By creating a new configuration package that contained the Customer table, and exporting the database data to an Excel spreadsheet, the data was replaced and the incorrect record was deleted. The spreadsheet was then reimported into the package, and the changes were applied to the database.

Louise's Bakery has completed their piloting, and is confident that the setup of the company is complete. The LouiseBakeryPILOT_2 company was copied over to the LouiseBakeryLIVE company, and the transactions were deleted from LouiseBakery_LIVE. The number series was reset, and the company status was changed to Production. Louise's Bakery is now ready to go live.

Prerequisites

Create a Clean Company

Louise's Bakery has worked with the initial LouiseBakery_PILOT1 company, but now wants to make some changes to the setup. Because of existing transactions, some of the setup cannot be changed, so you can create a copy the LouiseBakery_PILOT1  company, and then delete the transactions. The new PILOT company will be called LouiseBakery_PILOT2. This company with no transaction data is known as a clean company. You can repeat these steps as many times as you need to create PILOT companies.

Important: In order to delete database data from the Clean Company package, you need a developer license. A customer license does NOT have permission to delete data from all of the tables created in the Clean Company package.

To do this, you must perform the following steps:

To Copy an Existing Company

  1. In the Search box, enter companies, and then choose Companies.

  2. Select the company that you want to copy. In this case, LouiseBakery_PILOT1.

  3. On the Home tab, choose Copy. The Copy Company window opens.

  4. Enter the name of the new company. In this case, LouiseBakery_PILOT2.

  5. Click OK.

  6. Select the company that you just created.

  7. On the Actions tab, choose Change Company Status. The Change Company Status window opens.

  8. Select Testing for the New Company Status field. Companies with a status of Testing or Development allow you to delete data from the company.

  9. Click OK.

  10. Click Yes at the warning message. The status of the new company is Testing.

  11. Switch to the company that you just created. In this case, LouiseBakery_PILOT2.

To Create a Clean Company Package

This steps creates a package that contains the tables that typically contain transaction data, which is what you want to delete from your LouiseBakery_PILOT2 company.

  1. In the Search box, enter configuration packages, and then choose Configuration Packages.

  2. On the Actions tab, in the Functions group, choose Create Clean Company Package.

  3. In the Code field, enter the code to represent the Clean Company package. For example, CLEAN_PILOT2.

  4. In the Package Name field, enter the name that describes the package.

  5. Click OK. Once the package is created, the Config Package Card window opens. If required, you can now add any custom tables that you also want to delete data from, or remove any tables that you don't want to delete data from.

To Delete Data from the Company

This step deletes the data from the LouiseBakery_PILOT2 company using the CLEAN_PILOT2 package that you just created. Database data can only be deleted for companies that have a status of Testing or Development on the Companies window. This function does NOT run data deletion integrity checks.

Note: You can only delete data from tables that you are licensed to access.

You should be on the Config. Package Card for the CLEAN_PILOT2 package that you just created.

  1. On the Home tab, in the Process group, choose Delete Database Data.

    Warning: All data will be deleted in the listed tables. This process does NOT validate the data being deleted.

  2. Click Yes at the confirmation message. The data is deleted. This may take a while depending on the number of records.

    3. The transaction data has now been removed from your LouiseBakery_PILOT2 company, and you can now adjust your setup.

To Import Opening Inventory

When the database data was deleted, all of the inventory was deleted as well.  You need to reimport the opening inventory.

The OPENING INVENTORY package allows you to add inventory. The Item Journal table, in the OPENING INVENTORY package, allows lot numbers, item expiration dates and net weights to be loaded into the Item Journal, and then be posted without adding item tracking.

These steps are going to explain how to add lot numbers and net weights to the data without adding item tracking.

  1. In the Search box, enter configuration packages, and then choose Configuration Packages.

  2. Open the OPENING INVENTORY package. This package is added when a new company is created.

  3. Add the Net Weight field if you use variable weight items. You need to select the Net Weight field so that it is visible on the Excel spreadsheet.

    1. Select the Item Journal Line record.

    2. In the Tables FastTab, choose Table>Fields. The Config. Package Field window opens.

    3. Select the Include Field check box for the Net Weight field (23019000).

    4. Click OK.

  4. Export the Item Journal Line records to Excel.

    Note: Prior to performing this step, you may want to populate the Item Journal window with the items that you want to add lot numbers to so that the correct data will already be in the Excel spreadsheet. The entry type should be Positive Adjmt.

    1. Select the Item Journal Line record.

    2. In the Tables FastTab, choose Excel>Export to Excel.

    3. Click Yes.

    4. Enter a name for the file, and click Save.

  5. Populate the Excel spreadsheet.

    1. Open the Excel spreadsheet that you just created.

    2. Delete any lines that you are not updating.

    3. Make note of the Journal Batch Name.

    4. Enter values in the Lot No. field.

    5. If using, enter the total net weight of the item in the Net Weight field.

      Note: If the Net Weight field is blank, then the net weight will be inherited from the item card. If the Net Weight field is zero (0), then the net weight will remain zero.

    6. Enter TRUE in the Post Using Line-Level Lot No. field for all items that you want to add the lot number and net weight to. This will allow you to post the records from the Item Journal without having to add item tracking.

    7. Save the spreadsheet.

  6. Import the Excel spreadsheet.

    1. From the OPENING INVENTORY Config. Package Card window, select the Item Journal Line record.

    2. In the Tables FastTab, choose Excel>Import from Excel.

    3. Select the Excel spreadsheet that you just saved, and click Open. The value in the No. of Package Records field is updated once the spreadsheet is imported.

  7. Review the data.

    1. Select the Item Journal Line record.

    2. In the Tables FastTab, choose Table>Package Data.

    3. Review the data.

    4. If required, update the data.

    5. Click OK. The Config. Package Records window closes.

  8. Apply the data.

    1. Select the Item Journal Line record.

    2. In the Tables FastTab, choose Functions>Apply Data.

    3. Click Yes.

    4. Click OK.

  9. Post the items to inventory.

    1. Open the Item Journal window.

    2. Select the Batch Name that corresponds with the batch name in the Excel spreadsheet.

      Note: If you want to see the lot number and net weight associated with the line, click the Down arrow beside the Home tab, select Help>About this Page, and find the Lot No. and Net Weight fields on the Table Fields FastTab.

    3. On the Home tab, choose Post.

    4. Click Yes to post the journal lines.

      The items have now been added to inventory.

Delete Data from the Database Using Package Data

After working with the data, Louise's Bakery discovered that the YOGIES items were incorrectly added to the database, and need all of the YOGIES items need to be deleted.

By creating a new configuration package that contains the Item table, and copying the database data to the package, you can then delete the YOGIES items, and then apply the deletions to the database.

Note: You can only delete data from tables that you are licensed to access.

This section contains the following procedures:

Prerequisites

  • A clean company was created if any of the data to be deleted was used in transactions.

To Create a Configuration Package and Copy the Database Data

 This step creates a configuration package that contains the Item table, and copies the YOGIES records from the database to the package. The data is validated as it is copied to the package.

  1. In the Search box, enter configuration packages, and then choose Configuration Packages.

  2. Create a new configuration package.

    1. Click New. The Config. Package Card window opens.

    2. In the Code field, enter ITEMS.

    3. In the Package Name field, enter Items.

    4. Select the Exclude Config. Tables check box.

    5. In the Tables Fast Tab, select 27 in the Table ID field.

  3. Filter on the YOGIES description. This step filters the records so that only items with "YOGIES" in the description will be copied to the package.

    1. In the Tables FastTab, select Table>Filters. The Config. Package Filters window opens.

    2. Enter 3 in the Field ID field.

    3. Enter *YOGIES* in the Field Filter field.

    4. Click OK.

  4. Copy the database data to the package.

    1. On the Home tab, in the Process group, choose Get Database Data. The data is validated as it is copied.  This may take a while depending on the number of records.

      All of the records with "YOGIES" in the description are copied to the package. In the Tables FastTab, the No. of Package Records field will show the number of records that were successfully copied, and the No. of Package Errors field will show if any errors occurred with the copied data.

  5. To view the package data, go to Table>Package Data.

To Delete Database Data

These steps allow you to delete data from the database by updating the package data, and then applying the package data to the database. The deletion is validated to ensure that other data will not be affected.

You should still be on the Config. Package Card for ITEMS.

  1. Choose Table>Package Data. The Config. Package Records window opens.

  2. On the Home tab, in the Process group, choose Set Delete in Database. This selects all of the Delete in Database check boxes.

  3. Select all of the records.

  4. On the Home tab, choose Apply Data. JustFoodERP will validate that the records can be deleted, and then, if no data is compromised, all of the YOGIES records, where Delete in Database is selected, will be deleted from the database.

Update and Delete Data Records using an Excel Spreadsheet

Louise's Bakery wants the customer address to have a consistent format. The first step, in making addresses consistent, is to change all references of "PO boxes" to "P.O. Boxes". This can be done using an Excel spreadsheet, which allows you to perform a search and replace. While using the Excel spreadsheet, you can also delete any customer records that were added incorrectly.

By creating a new configuration package that contains the Customer table, and exporting the database data to an Excel spreadsheet, you can search and replace data, and delete any incorrect records. You can then reimport the spreadsheet into the package, and apply the changes to the database.

Note: You can only delete data from tables that you are licensed to access.

This section contains the following procedures:

To Create a Configuration Package

This step creates a configuration package that contains the Customer table.

  1. In the Search box, enter configuration packages, and then choose Configuration Packages.

  2. Create a new configuration package.

    1. Click New. The Config. Package Card window opens.

    2. In the Code field, enter CUSTOMERS.

    3. In the Package Name field, enter Customers.

    4. Select the Exclude Config. Tables check box.

    5. In the Tables Fast Tab, select 18 in the Table ID field.

    6. Click OK.

To Update and Delete Data from an Excel Spreadsheet

These steps allow you to export the database data to an Excel spreadsheet, make any changes to the data including deletions, import the spreadsheet in to the package, and then apply the changes to the database.

  1. In the Search box, enter configuration worksheet, and then choose Configuration Worksheet.

  2. Delete any existing lines in the worksheet.

  3. In the Table ID field, select the table that has data that you want to change. In this case, 18 for the Customer table.

  4. On the Actions tab, in the Functions group, choose Assign Package.

  5. Select the CUSTOMERS package, and click OK.

  6. Select the Import Type as Column field. This adds the Import Type column at the end of the Excel spreadsheet, which allows you to define if the record should be deleted.

  7. Modify the fields to display in the Excel spreadsheet:

    1. On the Actions tab, in the Show group, choose Fields. The Config. Package Fields window opens.

    2. On the Home tab, choose Clear Included. This clears all of the Include Field check boxes.

    3. Select Include Field for the following lines: No., Name, Address, Address 2, City and Import Type.

      Important: Make sure that Include Field is selected for the Import Type line. If you filter on Import Type=Yes, you can find the line quickly, and make sure that the Include Field check box is selected. If Include Field isn't selected for the Import Type line, then it won't be in the Excel spreadsheet.

    4. Click OK. You are returned to the Config. Worksheet.

  8. Export the data to Excel.

    1. On the Actions tab, in the Excel group, choose Export to Template.

    2. Click Yes to export the data to the Excel spreadsheet.

    3. Enter Customers as the name for the spreadsheet and click Save. The Excel spreadsheet opens.

  9. Update the data in the Excel spreadsheet.

    1. Open the spreadsheet.

    2. Search for "P O Box".

    3. Replace with "P.O. Box".

    4. For the first record, enter Delete in the Import Type Code column. When this data is applied from JustFoodERP, this record will be deleted from the database as long as no other data is impacted.

    5. Save the spreadsheet.

  10. Import the Excel spreadsheet.

    1. Return to the Config. Worksheet window, and on the Actions tab, in the Excel group, choose Import from Template.

    2. Click Yes to import the data to the package.

    3. Navigate to the spreadsheet, and click Open.

      You can view the package data. The record to be deleted will have the Delete in Database check box selected.

  11. Apply the changed data to the database.

    1. Select all of the records

    2. On the Actions tab, in the Functions group, choose Apply Data.

    3. Click Yes to apply the data.

      JustFoodERP will validate that the records can be deleted, and then, if no data is compromised, the record marked where Delete in Database is selected, will be deleted from the database.

Create a Live Company

Louise's Bakery has completed their piloting, and is confident that the setup of the company is complete. Now you need to create a transaction-free copy of the latest pilot company that you can go live with.

To do this, you must perform the following steps:

To Copy an Existing Company

  1. In the Search box, enter companies, and then choose Companies.

  2. Select the latest PILOT company that you want to copy.

  3. On the Home tab, choose Copy. The Copy Company window opens.

  4. Enter the name of the new company. In this case, LouiseBakery_LIVE.

  5. Click OK.

  6. Switch to the LIVE company that you just created. In this case, LouiseBakery_LIVE.

To Create a Clean Company Package

This steps creates a package that contains the tables that contain transaction data, which is what you want to delete from your LouiseBakery_LIVE company.

  1. In the Search box, enter configuration packages, and then choose Configuration Packages.

  2. On the Actions tab, in the Functions group, choose Create Clean Company Package.

  3. In the Code field, enter the code to represent the Clean Company package. For example, CLEAN_LIVE.

  4. In the Package Name field, enter the name that describes the package.

  5. Click OK. Once the package is created, the Config Package Card window opens. If required, you can now add any custom tables that you also want to delete data from, or remove any tables that you don't want to delete data from.

To Delete Data from the Company

This step deletes the transaction data from the LouiseBakery_LIVE company using the CLEAN_LIVE package that you just created. Database data can only be deleted for companies that have a status of Testing or Development on the Companies window. This function does NOT run data deletion integrity checks.

Note: You can only delete data from tables that you are licensed to access.

You should be on the Config. Package Card for the CLEAN_LIVE package that you just created.

  1. On the Home tab, in the Process group, choose Delete Database Data.

    Warning: All data will be deleted in the listed tables. This process does NOT validate the data being deleted.

  2. Click Yes at the confirmation message. The data is deleted. This may take a while depending on the number of records.

    3. The transaction data has now been removed from your LouiseBakery_LIVE company.

To Import Opening Inventory

Once you're confident that your inventory levels will not be changing prior to going live, you can enter your final opening inventory.

See Import Opening Inventory.

To Reset the Number Series

This step clears the number series that were incremented during piloting. 

  1. Create a new configuration package.

    1. Click New. The Config. Package Card window opens.

    2. In the Code field, enter NUMBER_SERIES.

    3. In the Package Name field, enter Number Series.

    4. Select the Exclude Config. Tables check box.

    5. In the Tables Fast Tab, select 309 in the Table ID field.

    6. Click OK.

  2. Export the data to Excel.

    1. From the Tables Fast tab, choose Export to Excel.

    2. Click Yes to export the data to the Excel spreadsheet.

    3. Enter Number Series as the name for the spreadsheet and click Save. The Excel spreadsheet opens.

  3. Update the data in the Excel spreadsheet.

    Warning: Make sure that the corresponding tables using the number series have had their data cleared, otherwise resetting the number series may result in duplicate entry errors.

    1. Open the spreadsheet.

    2. Clear the data in the Last No. Used and Last Date Used columns for the number series where corresponding tables have had their data cleared.

    3. Save the spreadsheet.

  4. Import the Excel spreadsheet.

    1. Return to the Config. Package Card window, and from the Tables Fast tab, choose Excel>Import from Excel.

    2. Click Yes to import the data to the package.

    3. Navigate to the spreadsheet, and click Open.

  5. Apply the changed data to the database.

    1. On the Home tab, in the Process group, choose Apply Package.

    2. Click Yes to apply the data.

    6. The number series have now been reset, and records will be numbered according to the starting number in the series.

To Change the Company Status to Production

This step changes the status of the company to Production.

  1. In the Search box, enter companies, and then choose Companies.

  2. Select the LIVE company. In this case, LouiseBakery_LIVE.

  3. On the Actions tab, choose Change Company Status.

  4. Select Production in the New Company Status field.

  5. Click OK.

    6. The status of the LIVE company is now Production, and Louise's Bakery is ready to start using the LouiseBakery_LIVE company.

Video

See Also


© 2016 Microsoft and IndustryBuilt Software. All rights reserved.

JustFoodERP 2015 Feature Pack 3 (8.00.03.00) | August 2016