Displaying the name of the ID value on a form

asembereng

I want to store the ID value of reference table to another table but i want the form to display the actual name instead of the ID number.. For example, I want store the department ID on an employee table but on the employee card it should display the name of the department.. Any idea please?

kapamarou

Lookup the usage of flowfields. Maybe Lookup would be the function of the flowfild...

asembereng

I am using the lookup function but it stores and display the ID; eg. 1 for the department name IT department.. What i want is to store the department ID but displays the department name. The user will see the department name instead of seeing the department ID.

kapamarou

You have a "Real" field that has a table relation to another table's ID field. When you look that up it gets filled with the ID. You add a new field (not a real Field in SQL database) and you set the Class to Flowfield and it's function to be something like: Lookup Department.Description where Department.ID = field(ID). If you use the Assist edit button you just have to fill the values...

kapamarou

Change the FieldCLass to FlowField and then set the CalcFormula... I don't have it in front of me now to get a screenshot...

asembereng

I tried that and i am having some data type errors. Anymore idea on what to or am i doing it wrongly?

kapamarou

Can you show us what you did?

pri_1_ranjan

Probably at the card form do not use the actual field of the table. Instead use a function [return type as text] as a source expression of the 'text box' that will return the name, of the ID that you have coptured at the table level.
But you should try avoiding using this at the list form, as it may degrade performance. It also cannot be used for filtering data.
Hope this helps.

asembereng

ok can you explain, i am pretty confuse now.. Would you mind giving me example.. like coding it?
Thanks

Savatage

are there seperate fields for the ID name & id Code or are you trying to get the name FROM the ID Code?

If it's store seperately
ID -- Name
1 -- Some Name
2 -- Some Other Name

then the flowfield would look like
Method=Lookup
Table=table that has the ID name & Code
Field = Id Name
Table Filter= ID Code = Salesperson.ID Code

Savatage

I want store the department ID on an employee table but on the employee card it should display the name of the department

Again as I re-read - where ias the name stored - in some department table?

asembereng

Yes i have a table called department with fields ID and Department_name. On the employee table there is a foreign key for the department_ID which stores the ID but i want the user to see the department name even though the department ID is stored.

kapamarou

In Employee Table Add:

1) Normal field, DepartmentID, type = the type of the field in the department table.

2) Flowfield, DepartmentName with CalcFormula = Lookup(Department.Name WHERE (ID=FIELD(department_ID)))

On the form reveal both fields. When the user fills the departmentID field the DepartmentName field is calculated Automatically.

After that, if you want you can remove the department_ID from the form and create some lookup code on the OnLookup code of that field that will actually fill the DepartmentID field..

MBerger

kapamarou wrote:

On the form reveal both fields. When the user fills the departmentID field the DepartmentName field is calculated Automatically.

This is not entirely true, you'll have to add a Calcfields(DepartmentName) to the OnValidate trigger of DepartmentID ( in the table ). Otherwise you'll only see the flowfield update AFTER you leave the record.

asembereng

2) Flowfield, DepartmentName with CalcFormula = Lookup(Department.Name WHERE (ID=FIELD(department_ID)))

Ok should this be on the employee table? Am i going to create another field called DepartmentName on the employee table? Because i only want to store the ID on the employee table and yet display the departmentname on the card.

MBerger

asembereng wrote:

2) Flowfield, DepartmentName with CalcFormula = Lookup(Department.Name WHERE (ID=FIELD(department_ID)))

Ok should this be on the employee table? Am i going to create another field called DepartmentName on the employee table? Because i only want to store the ID on the employee table and yet display the departmentname on the card.

if you only want to show it on the card, and not use it ANYWHERE else, then you could just add some code to the OnAfterGetRecord of the card form to get the name and put it in a variable which you then show on the form.

asembereng

Now i am getting the name to display on a text field using:

dept.get("department id");
deptname := dept."department name";
on onAfterValidat() trigger.

My problem is if i move to the next employee the departname does not change unless i select it again. Is there any possible way that the text field moves whenever i clicked on the next employee?

asembereng

I am also getting an error saying department id 0 does not exist. I when to the department table but there was no zero id.. I am wondering where is this zero id coming from.