Currently, I have a client number (will be named customer_code in ERP), which corresponds to one of the multiple projects each of our client has, which is used to link to another table with first column named (key) Job Number. This job number is primary key in an access table that lists the client name, project description, project location, billing data and budget.
The client number (customer code) really designates multiple individual projects under each unique clients (so we still need a master number for the clients themselves), where we also have a corresponding Job Number keyed to Client Numbers in a many-to-one relationship…the job number links to the individual billing in quickbooks for each project (we are an engineering company, we sell consulting services and no products)wheras the client number links to things like the billing addresses, contact info, emails etc, CRM stuff
The Client Number from our access database is what I have named the customer code for ERPnext
The Job Number and Client Number are first column primary keys as you can see
They in effect tie our Billing, Clients and Individual Projects (many clients have multiple concurrent projects running) all together in Access. We have no project management system and that is one thing I am wanting ERPnext to do for us, so I need to be able to tie the Job Numbers and the billing, timesheets and CRM components together somehow using these.
The question is should I create a custom field and designate these ‘data’ types, or create a Custom Doc-Type and then make them ‘links’?
Thanks for your help, I am new to ERPnext but pretty familiar with apache and linux in general. Know php but not much python yet!
You should create a custom field (database column) for this. Do it by customizing, and it should then be accessible in your data import. You may need to restart the bench and/or migrate for it to show up in the .xls file.
Ok thank you. Will this then interact with the Job Number as well, as in the photo? The job number references the client number to tie the billing together… so I will create 2 custom fields, ultimately, one for the customer code (what the client ID is currently doing in Access) and also another in the next import for projects and Billing, for the Job Number?
So these will both be ‘data’ selections as opposed to ‘link’ and then I will need a custom script to talk between the tables, or will ERPnext do that you think?
So these are the two main tables I am working on now, and I am trying to figure out how to build the relationship between the two so i can start feeding in the timesheets and also build out the project management module.
You have a mismatch here in that ERPNext is expecting Projects (and Sales Orders, etc) to reference a Customer by name, not by number.
If you can live without the customer number your life is easier, just create your projects based on the job number and link to the customer by name.
Unless customer number is the only unique identifier for your customers, you don’t need it.
That makes sense to me. One thing is that the Client Number specifies a specific project that is billed under a customer, hence one customer may have 4 or 5 ‘client numbers’, each which is actually tying to a specific separately billed project. For example, the City of Hood River may have 5 or 6 client numbers for respective active projects, and these are billed separately but are still under the general umbrella of this client … would the unique ID generated by ERP in that first column work to do this chore do you think? Would the ERP customer ID essentially perform the same task as the Client Number did in the access database I am migrating from?
Or would it be easier to somehow change the actual client names to reflect each new project? For example have
City of Hood River - Water Project
City of Hood River - Sewer Line435
City of Hood River - Safe Walk to Schools
City of Hood River - Sanitation pump #34
City of Dallesport - Waterline 4
City of Dallesport - Sewerline Redesign
Hiawette Construction - Project 23
Hiawette Construction - Project 24
Hiawette Construction - Project 25
…etc
Whereas now, in the old Access db, we have assigned a ‘client number’ to each separate project and then pull all those numbers together in QuickBooks for final billing invoices to each specified billing party
Would the ERP customer ID essentially perform the same task as the Client Number did in the access database I am migrating from?
Customer Name would, yes. The programming concept you’re looking for here is primary key which means exactly what it sounds like.
would the unique ID generated by ERP in that first column work to do this chore do you think?
There isn’t a unique ID generated for customer, but you can set a naming series to include customer in the naming of the Project, but it will still want a slot in the naming series to increment.
customer.-.project_name.-.### for your naming series should do it.
Hey awesome! That really helps me understand the schema here - so do you think it would work to use the naming series as a way of adding that unique client ID to the name? (I see it is sequential so would not)
Also, it bears mentioning that only 5-6% of our clients have multiple client IDs, and over 90% only have one client number which matches their name column
My other idea is just to make more rows and repeat the client’s name but with a unique identifier to that particular project … I think this is the idea you capture with ‘customer.-.project_name.’ would I need to add the naming series there to keep it straight, or would the unique project names do it?
Honestly, you’re getting hung up on both the job number and client ID. This is a normal part of migration from one system to another, but neither are required in this context.
Your customers’ primary key just becomes:
City of Hood River
City of Dallesport
Hiawette Construction
Projects are normally manually named, but in your case it sounds very prudent to have some sort of schema, which is why I’m suggesting a naming series for Projects. It would enforce something like this:
City of Hood River-Water Project-001
City of Hood River-Sewer Line435-001
City of Hood River-Safe Walk to Schools-001
City of Hood River-Sanitation pump #34-001
City of Dallesport-Waterline 4-001
City of Dallesport-Sewerline Redesign-001
Hiawette Construction-Project 23-001
Hiawette Construction-Project 24-001
Hiawette Construction-Project 25-001
Got it - that makes good sense. Thanks for taking the time to explain it! That looks like what I was trying to figure - it wont matter that we begin a new ‘customer code’ series, just that we have a bit of extra data to tie it all together.
Got it - thanks for your input, sends me down the right road for us!
Hello and thanks for your help! I am having a problem figuring out where to go to do this step, when i go to the naming series screen, it doesnt include ‘projects’ in the dropdown as an option to add a naming series … should I go somewhere else?
Ok I added a custom field and now have this, but it still not showing up as ‘mandatory’ field when I download template to start import - not sure what I am missing!
For the custom field to show up in the template you have to run bench migrate which matches the database schema throughout the application, then the custom field will show up.
