Introduction
This article will provide you with information on how to use the support tool - user migration
- This article describes the user migration process.
- This article will be useful to PG Champion, Club Owner, System Administrator.
Before you start
If you use automation events, the system will not run them right away during migration.
Automation Rules that use time settings will run later, after the migration is finished.
Instruction
To access the migration tools to upload users, go to PGM -> Settings -> Support Tools.
Go to the Migration tab, then press Download template file and User template to download the XLSX template for a user migration.
Open the spreadsheet template using a compatible software.
Some columns may not be listed on the template file.
Fields on spreadsheets
Listed below is a list of all of the attributes required. The column names cannot be changed as this is required to connect the upload file to the fields within PerfectGym.
You may delete some headings if they are not required for the upload.
ExternalId
These columns will provide a link between a previous system/database to the current system.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| ExternalUserId | Member's Id number from external system | Yes | Yes | Text | This needs to be a unique Id relating to the club member from your previous system. |
| ExternalContractId | Contract Id number from external system | No | Yes | Text | This needs to be a unique Id relating to the club members contract from your previous system. If the club member has more then 1 contract, then the ExternalUserId needs to be the same and all personal data needs to be copied on each line. |
Club member information
These columns will provide details about the club member.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| UserNumber | Member's number in PerfectGym system after migration | No | No | Text | Club member usernumber which will be displayed on the PerfectGym system, if left blank the next number in the series will be used. |
| ClubNumber | Club Number | Yes | Yes | Text | Club with given number must exist in PG system. This can be found in PGM->Clubs->List of clubs. |
| Name | Member's first name | Yes | Yes | Text | First name of the club member |
| LastName | Member's last name | Yes | Yes | Text | Last name of the club member |
| SecondName | Member's second name | No | Text | Second name of the club member (note field is not shown as default) | |
| Sex | Member's sex | Yes | Yes | Text | Male, Female |
| BirthDate | Member's birthdate | Recommended | Recommended | Date | Club member date of birth. This needs to be entered in as a date format - eg. 20/05/2010 (dd/mm/yyyy) Localised date formats on excel are compatible. |
| Member's email | Recommended | Recommended | Text | Club member email address, this will allow login to client portal, email needs to be unique. | |
| Phone | Member's home phone | No | No | N/A | No longer in use - use MobilePhone |
| MobilePhone | Member's mobile phone | No | No | Text | Mobile phone number. It is recommended to use area code + number. Eg +61499999999 |
| PhotoUrl | Url to a photo of a member | No | No | Text (as URL) | URL location of the club members photo, this will upload the photo into the database. |
| Street | Member's address - street | No | No | Text | Club member street number/name |
| PostalCode | Member's address - zip code/postal code | No | No | Text | Club member postal code. |
| City | Member's address - city | No | No | Text | Club member city/suburb |
| State | Member's state | No | No | Text | Club member state (required if country is divided into states). Use full name of state. |
| Country | Member's address - country | No | No | Text | Club member country, use full name of country. |
| MemberCardNumber | Member's club card number (not credit card) | No | No | Text | Club member access card number, only one card number can be loaded. |
Additional information about club member
If using corporate module, Personal Id or document number on your site, these fields will need to be filled in where required.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| Company | Company of a Member's | No | No | Text | If using the corporate module, this will add the club member to the company. |
| PersonalId | Member's personal Id | No | No | Text | Club member personal Id (such as national registration card number). |
| DocumentNumber | Member's document number | No | No | Text | Club member document number |
| DocumentIssuedBy | Member's document issuing party | No | No | Text | Club member document issue by, value must match to what is loaded on system. |
| DocumentIssueDate | Member's document issue date | No | No | Date | Club member document date field. This needs to be entered in as a date format - eg. 20/05/2010 (dd/mm/yyyy) Localised date formats on excel are compatible. |
Settings for club member
These fields are required to identify who is a guest (club member without any contracts) or a member (club member with a contract, regardless if expired or not).
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| Consultant | Consultant of the member | No | No | Text | The employee who is assigned to this club members profile. The full name (first name + last name) of the employee |
| IsActive | Is member active | Yes | Yes | BIT (1,0) | Is the member an active member on the system (ie. Will make purchases, visit, allow entry, contract holder), 1 = Yes, 0 = No. If unsure please set this as 1. |
| IsGuest | Is member a guest, or has a contract | Yes | Yes | BIT (1,0) | If member is classed as a guest (no contract added) 1 = Yes, 0 = No. |
Notes/Tags
Optional fields to add user notes or tags to the club member.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| Comment1 | Comment | No | No | Text | Create a user note on the club member profile (max 250 characters) |
| Comment2 | Comment | No | No | Text | Create a user note on the club member profile (max 250 characters) |
| Comment3 | Comment | No | No | Text | Create a user note on the club member profile (max 250 characters) |
| Comment4 | Comment | No | No | Text | Create a user note on the club member profile (max 250 characters) |
| Tags | Member's tags, separated with comma or semicolon. If not exists in PG it will be created automatically | No | No | Text |
Custom attributes
If you wish to upload data to Custom Attributes, please use the following set up. If you have multiple custom attributes to upload, please create a new column for each custom attribute. Multi-select dictionary lists cannot be uploaded here, please contract support to get this data uploaded.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | DataType | Information about column |
| CustomAttribute."Attribute Name" | CustomAttribute."Attribute Name" (to upload custom attributes) | No | No | Text | If you wish to upload custom attributes, you will need to add the custom attribute name after the word "CustomAttribute.". For example if the custom attribute field name is 'FP Number' Then the header will be named "CustomAttribute.FP Number" |
Contract information
This will upload the contract of the contract based on the values set on this template.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column | |
| TodayBalance | Current financial balance | No | No | Number (Currency) | If no value is provided then the member will be migrated with debt. This is from all contract transactions generated from the start date of the contract to todays date. If migrating and want no debt on the contract, please enter 0 here. Debt should be set as a negative amount, credit as a positive amount. | |
| PrepaidBalance | Current balance of member's prepaid account | No | No | Number (Currency) | This will load a value in prepaid balance field. If left blank then no value will be loaded. | |
| SignUpDate | Contract signup date | No | No | Date | If no value is provided then signup date will be set equal to start date | |
| StartDate | Contract start date | No | Yes | Date | Contract start date. Transactions will generate from this date. | |
| EndDate | Contract end date | No | No | Date | Contract end date. Transactions will not continue from this date. | |
| MinCancelTimeMonths | Minimum number of months before contract cancelation | No | No | Number (Values between 1-12) | This will add the amount of months from start date to the minimum commitment period. | |
| IsProrata | Does contract have a prorata | No | Yes | BIT (1,0) | This will calculate the pro-rata from the start date to the first debit date. 1 = Yes, 0 = No. | |
| IsEndProrata | Does contract have an end prorata | No | Yes | BIT (1,0) | This will calculate the pro-rata from the last debit date to the end date of the contract. If the contract has no end date, this will determine if a pro-rata will be calculated when the end date is added to the contract. 1 = Yes, 0 = No. | |
| ProrataDay | What day of month is prorata caltulated from | No | No | Number (Values between 1-31) | For monthly recurring contracts only, this will determine when the pro-rata will be calculated from. For all Australian and New Zealand clients, this field is not used. Value must be between 1 and 31 | |
| ForcePaymentPlan | If true, payment plan will not be created based on migration file, but rather taken as a pre existing payment plan in the system | No | No | BIT (1,0) | This will force the system to use a pre-configured payment plan already set up on the system. If this is set to 1, most other fields in contract is not mandatory. 1 = Yes, 0 = No | |
| PaymentPlanAutoName | If true, payment plan will be automatically named based on its parameters from migration file | No | No | BIT (1,0) | This will create a new payment plan (set as inactive) based on the values provided on this upload, payment plans will not be duplicated if the same values are used. If ForcedPaymentPlan is set to 1, this needs to be set as 0. 1 = Yes, 0 = No | |
| Membership | Name of membership | No | Yes | Text | Full name of the access rule, this is found in PGM->Sale->Access Rules. | |
| PaymentPlan | Name of payment plan | No | Yes | Text | Name of the payment plan. If using ForcePaymentPlan the name of the payment plan must match to what is in PGM->Sale->Payment Plans. | |
| PaymentPlanType | Name of payment plan type | No | Yes | Text | The plan type which is connected to the payment plan. | |
| TimePeriod | Is contract time interval based on days or months | No | Yes | Fixed Text (Day, Month) | This is the time period set for both ContractLength and ContractFrequency. Values are only Day or Month. If the payment interval is fortnightly or weekly. This value must be set as Day and ContractFrequency field is set as either 14 or 7 (based on number of days set in interval) | |
| ContractLength | Number of Time Periods in a contract | No | Yes | Number (Value > 0) | Contract length, this is the number of days/month (based on value used in TimePeriod) in the contract. | |
| ContractFrequency | Number of Time Periods in 1 payment interval | No | Yes | Number (Value > 0) | This is the payment interval of the contract, set as the number of days/months (based on the value used in TimePeriod). | |
| MembershipFee | Membership fee | No | Yes | Number (Currency) | This is the membership fee per payment interval (ContractFrequency). Default setting is inclusive of Tax. | |
| VatRate | Vat rate | No | Yes | Number (must match what is on System in PGM->Settings->VAT Rates, do not add % symbol) | This is the tax rate set on the system. This is found in PGM->Settings->Support Tools->VAT Rate. Do not add % to the value. | |
| AdminFee | Administrative fee of a contract | No | No | Number (Currency) | Admin fee set on the contract as a one off charge. | |
| AdminFeeVatRate | Administrative fee vat rate | No | No | Number (must match what is on System in PGM->Settings->VAT Rates, do not add % symbol) | This is the tax rate set on the system. This is found in PGM->Settings->Support Tools->VAT Rate. Do not add % to the value. | |
| PaymentType | Type of payment for the contract | Yes | Yes | Fixed Text (Cash, CreditCard, DirectDebit, VirtualAccount, MoneyTransfer, Company) | This is the payment method set on the club member profile. By default this should be set as Cash. If using any other method apart from cash then please provide the correct information in the following field where required (May not be compatible with selected Payment Providers) - Possible values (Cash, CreditCard, DirectDebit, VirtualAccount, MoneyTransfer, Company) | |
| StartChargingFromDate | Date of first payment of a contract | No | No | Date | This will set the date when the first payment can be made. | |
| StartGeneratingTransactionsFromDate | PG will not generate transactions for contract before this date - this works on payment plan level | No | No | Date | This will not generate any transactions prior to this date. Not recommended to use as it will remove selected history of transactions if backdating. | |
| IsUpfront | Is upfront payment checked for paymnet plan | No | No | BIT (1,0) | Is the payment plan an upfront / paid in full payment plan, If this is a recurring debit payment plan, set this to 0. If set to 1, this will move all due dates of the transactions to the sign up/start date. 1 = Yes, 0 = No. | |
| IsPaymentChosenDay | Is payment charged on a chosen day of month. If false the payment will be charged on 1st day of month | No | No | BIT (1,0) | If the payment plan recurring debit is set on a fixed day (eg. Billing every Thursday, billing 20th month) Set this value to 1. If the recurring billing cycle is set from the contract start date, or the contract is an upfront contract, set this value to 0. 1 = Yes, 0 = No. | |
| IsAutomaticallyEnded | If true, the contract will end automatically after reaching minimal contract length | No | No | BIT (1,0) | Will the contract have an end date automatically added to the contract based on the Start date and the contract frequency. 1 = Yes, 0 = No. | |
| IsAdditionalContract | Is contract an additional contract | No | No | BIT (1,0) | Is the contract an additional payment plan (where the club member has a main contract. 1= Yes, 0 = No. | |
| SynchronizeWithContract | Used on add-on contracts to synchronise with main contract | No | No | BIT (1,0) | If the field IsAdditionalContract is set as 1, will this link the payment plan to the club members main contract. 1= Yes, 0 = No. | |
| StopChargingAfterMinPeriod | If true, user will not be charging after reaching a minimal contract length | No | No | BIT (1,0) | After the minimum commitment period has passed, then contract will not generate any more charges. This is designed for upfront contracts only. 1 = Yes, 0 = No. | |
| FreezeAvailable | Is freezing available for the contract | No | No | BIT (1,0) | Can the contract be put on freeze, 1 = Yes, 0 = No. | |
| AutomaticRenew | Is contract automatically renewed after ending | No | No | BIT (1,0) | Will the contract be automatically renewed (to the same payment plan) 1 = Yes, 0 = No. (note EndDate must be filled out in order for Autorenew to work) | |
| VisitLimit | Limit of number of visits for given paymentplan | No | Yes, if VisitLimit is defined | Number (Values between 1-99) | Number of visits per VisitPeriod the club member has on their contract. | |
| RemainingVisitCount | Remaining visits | No | Yes, if VisitLimit is defined | Number (Values between 1-99) | Remaining number of visits the club member has in the VisitPeriod on this contract. | |
| VisitPeriod | Time period per which visits are granted | No | Yes, if VisitLimit is defined | Fixed Text (Day, Week, Month, Infinity) | The visit period interval. The values used are Day, Week, Month, Infinity. |
Payer information
This will only be added if a different member is paying for the contract.
Payer Information cannot be used within Australia or New Zealand migrations.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| PayerName | If contract is paid by someone other than the member - Name of that person | No | No | Text | |
| PayerAddress | If contract is paid by someone other than the member - Address of that person | No | No | Text | |
| PayerPostalCode | If contract is paid by someone other than the member - PostalCode of that person | No | No | Text | |
| PayerCity | If contract is paid by someone other than the member - City of that person | No | No | Text |
Account number and Virtual Account number
This can only be used if PaymentType = "VirtualAccount"
Account number and Virtual Account number cannot be used within Australia or New Zealand migrations.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| AccountNumber | Member's account number | No | No | Text | |
| VirtualAccountNumber | Member's virtual account number | No | No | Text |
Bank account upload
This will upload banking details to the club member profile. This can only be used if PaymentType = 'DirectDebit'
For all Australian and New Zealand bank accounts, please use the fields in "Bank account upload (Australia and New Zealand)". This field may not be compatible with all payment providers. Please contact support prior to doing any uploads.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| BankAccountBic | Bank account code | No | No | Text | |
| BankAccountMandatoryId | Direct debit mandate id | No | No | Text | |
| BankAccountFirstPayment | Set true to declace that user had no direct debit payments prior to migration | No | No | Text | |
| BankAccountMandatorySignUpDate | Date of direct debit agreement signing | No | No | Date |
Bank account upload (Australia and New Zealand)
This will upload banking details to the club member profile for Australia and New Zealand clients. This can only be used if PaymentType = 'DirectDebit'
This field may not be compatible with all payment providers. Please contact support prior to doing any uploads.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| BankAccount | No | No | Text | ||
| BankAccountBSB | No | No | Text | ||
| BankAccountPayerName | No | No | Text | ||
| BankAccountPayerStreet | No | No | Text | ||
| BankAccountPayerCity | No | No | Text | ||
| BankAccountPayerPostal | No | No | Text |
Credit card upload
This will upload credit card details to the club member profile. This can only be used if PaymentType = 'CreditCard'
For all Australian and New Zealand credit cards, please use the fields in "Credit card upload (Australia and New Zealand)". This field may not be compatible with all payment providers. Please contact support prior to doing any uploads.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| CreditCardNumber | Credit card number | No | No | Text | |
| CreditCardReferenceNumber | Credit card reference number in case credit card number is not supplied | No | No | Text | |
| CreditCardExpityDate | Credit card expiry date | No | No | Date |
Credit card upload (Australia and New Zealand)
This will upload credit card details to the club member profile for Australia and New Zealand clients. This can only be used if PaymentType = 'CreditCard'
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| CreditCardNumber | No | No | Text | ||
| CardHolderName | No | No | Text | ||
| CardExpiryDate | No | No | Date |
Payment Token (Australia and New Zealand)
This will upload the payment token to the club member profile and register with the payment provider. This can only be used if PaymentType is either 'CreditCard' or 'DirectDebit'
For all Australian and New Zealand clients only. This field may not be compatible with all payment providers. Please contact support prior to doing any uploads.
| Column name | Description | Is Field Required (Guest) | Is Field Required (Contract) | Data Type | Information about column |
| PaymentToken | The payment token of the club member. | No | No | Text |
How to migrate data
When the document is ready, upload it and see if it works.
Click on Validate file.
Image
The validation result file will download, so you can see if there have been any errors made.
In this case, there is one missing value, which has to be corrected.
Enter the date you want your employees to start using the club from, i.e.: April 1st, 2022.
And repeat the validation process.
For the sake of clarity, remove the previous file with an error.
When the validation goes well, all column headers turn green.
As it happens so, you can expect that your members/employees are correctly migrated.
Also, the appearance of Migrate users is a good sign:
Click on Migrate users and check if the users have appeared in the system.
You will see them in the Members list in PGM->Clients->Club Members
