link record to another record in the same table

[MajP]

By the way..it seems i have found a bug?

It is not a bug, it is that you are using unrealistic data. You created your first organization on 6/18 put it only had Captial1 in the organization. Then you created a complete organization on 6/21. Your sales took place on 6/16. This would be impossible because you sold something before you had any agents. In real life you would create your agency and then go out and sell things. You could not sell things before you hired people to sell things. For the purposes of testing or if you are going to start importing existing data. You need to create the default organization before any sales.
1. Create a complete organization
2. This will automatically archive
3. Now go into the two archives and back date the OrganizationEffectiveDate prior to the beginning of sales. Set this date prior to any sales that you plan to process.

You should be able to add the Code field without problem. I would not do it the way you are doing it where you have an AgentName field with a code and a Web Code field with an agent name. Put the correct information in the correct field and modify the subforms to show the information properly.

 

[stefanocps]

ok for the "bug" i suspect it could be that actually

FOr the modification..how would you do that?

I am doing all the edit needed for my pourpouse and it is working fine though there is a point..i really need to use the code instead of the name in SALES..in all other forms, i want to see NAME, then as double check may be the code..but not really necessary once i know the method is SURE, i can use the code for sales then forget..may be i can see it in the structure view, or in the report...

 

[MajP]

I am doing all the edit needed for my pourpouse and it is working fine though there is a point..i really need to use the code instead of the name in SALES..in all other forms, i want to see NAME, then as double check may be the code..but not really necessary once i know the method is SURE, i can use the code for sales then forget..may be i can see it in the structure view, or in the report...

An Agent has a primary key
AgentPK
then other fields that describe the agent
AgentName
AgentCode
....

You can add more fields or delete fields since the database uses the AgentPK for all relationships and code. You might have to modify forms to show the Agent Code instead of the Agent Name or add additional fields to forms and reports.

ok for the "bug" i suspect it could be that actually

No it is "exactly" that. This will not work if you have sales dated before you created an organization. If I was making this a real application that I would distribute, I would probably have a feature to allow the user to easily choose the organization starting date. Now it just defaults to the day when you create the organization. If you want to back date the organization you have to go into the archive tables and do it manually.

 

[stefanocps]

An Agent has a primary key
AgentPK
then other fields that describe the agent
AgentName
AgentCode
....

You can add more fields or delete fields since the database uses the AgentPK for all relationships and code. You might have to modify forms to show the Agent Code instead of the Agent Name or add additional fields to forms and reports.


No it is "exactly" that. This will not work if you have sales dated before you created an organization. If I was making this a real application that I would distribute, I would probably have a feature to allow the user to easily choose the organization starting date. Now it just defaults to the day when you create the organization. If you want to back date the organization you have to go into the archive tables and do it manually.

i have thought to use one of those fields, agent id or pk..but they are too easy
When a user go to register in the site it canno thappen that register for a mistake
If a code is 1,or 2 or 3...it is easy to input a wrong code and having working the same
Thte code should be a bit more complex..at least a lettter and a number..as a real minimum

 

[MajP]

When a user go to register in the site it canno thappen that register for a mistake
If a code is 1,or 2 or 3...it is easy to input a wrong code and having working the same
Thte code should be a bit more complex..at least a lettter and a number..as a real minimum

Primary Keys are usually never seen and often not entered manually. So a when you enter a user to the database you simply add a name and code. When you select the "reportsTo" you could either display and select the senior Agent Code or senior Agent Name, but the table will store a related PK. You cannot make a mistake, because you select from a list and do not manually type values.

 

[stefanocps]

I am not sure we understand each other.a user in the website.need.a code to enroll in.this code belong to an agent, and this code cannot be simple as a only 1 digit number. So he.cannot use the id if the agent for example as it is too simple and might lead to unexpected behavuour

 

[MajP]

I am not sure we understand each other.a user in the website.need.a code to enroll in.this code belong to an agent, and this code cannot be simple as a only 1 digit number. So he.cannot use the id if the agent for example as it is too simple and might lead to unexpected behavuour

The code can be whatever you want. It has no relation to the AgentPK. In this database you should never see or care about the AgentPK. The Code can be visible and selectable.