JobTraking Database

[Fundi]

Hi Steven

A little while ago you send me a database (as a sql file) where you explained how some of my challenges could be solved. I studied yours and it helped me to some extend. One issue with using the Look up field is that you have to have the record before you do a lookup. In my case the customer calls you enter his details. Then you add the job and then you enter the job events.

Well, have a look if you are interested with what I have come up. I still have to do more reports and I have to do something about referential integrity. At the moment one could delete a job without deleting the events which would leave dormant events in the system.

Thank you for your excellent work
Fundi

[admin]

If you bring up the browse screen for customers (and you have enabled the add button.)

You can add the customer record and then choose it from the browse screen.

I had a look at your Job Form. You should never use a dropdown for a list that is not finite.
If you get 10000 customers, not only will you struggle to find the one you want, you will also massively increase the size of the html used to display this form.

Plus you can't search, the best you can do is pick the first letter of the customer's name.

You need to use a lookup because

• -a lookup can be searched
  -it can add new records on the fly
  -it only needs enough html to show 27 records at a time. (this limit allows for computers with limited memory and keeps transfer speeds fast)

For these reasons nuBuilder can be used (and is used) with tables holding hundreds of thousands of records or more.


Steven

[Fundi]

Hi Steven

Thanks for looking at my feeble attempts and commenting on it.

Your point is well taken but there is at least one major draw back about the lookup that make me use drop downs instead.
You can not hide the code field..

You see not even nuBuilder displays code fields. It uses drop downs everywhere even on list that are infinite (ie drop down to select on which form an object will be displayed).

I do not like to display codes to my users. It is not necessary to know a customer id or some custom code if you know the Name surname address of the customer.
I tried to set the display length of the code field to zero length but it will not allow this. I set it to 1 and it distorts the display.

One more hardship with Lookups. A lookup code field centers its contents and I have not found a way to make it align left or right. The problem is if you have a code system like:
Q-1
B-10
A-100
etc
and all the numbers in between it becomes very difficult for the user to find the number as the numbers do not line up.

Then imagine the lookup in a subform. It needs considerable more space then a drop down and will display the code.
I have even entered the description field in the code field and selected no description. Well that works except that the color of the field is yellow instead of white.
I hope you understand my points as I do yours.

I am open to solutions though. Perhaps I don't see it all. What I like about the Lookup is that I can decide what is displayed on a lookup and work with more than one field.
My solution gets a bit difficult if you have customers with the same name. So I have used now a lookup for my Customers. Displayed in yellow centered (not pretty but working). If any of these issues can be resolved it would be great. Perhaps others feel the same way.

Let the discussion go on.
Fundi

[admin]

Your point is well taken but there is at least one major draw back about the lookup that make me use drop downs instead.
You can not hide the code field..
Fundi

I don't know what this means.

One more hardship with Lookups. A lookup code field centers its contents and I have not found a way to make it align left or right. The problem is if you have a code system like:
Q-1
B-10
A-100
etc
and all the numbers in between it becomes very difficult for the user to find the number as the numbers do not line up.
Fundi

You find by filtering, if you know what code you want just type it in, if you don't, how is looking through a list going to help?

I think you are just going to figure out the reason for some of these things just by using nuBuilder.


Steven

[Fundi]

Hi Steven

With code field I mean the yellowish field in the beginning of the lookup. See attachement.

Screen shot

snapshot.png (25.71 KiB) Viewed 8037 times

Is there a simple way to suppress this code field?
Is there a way to change the color of the code field and the field after the button?

You find by filtering, if you know what code you want just type it in, if you don't, how is looking through a list going to help?

Sometimes you remember what the search item was called when you see it.
Example: Lets say I call a Form: Frm_Customer_Relations_Association
If I have to search for that and i don't remember the name quite correctly I may search for Frm_Relations or for Frm_Association and not find it. but rolling down even 50 form names will bring it back to me in an instant that it was Frm_Customer_Relations_Association. That is besides the point that I am much faster rolling down 50 or even 100 records with the mouse wheel then typing and clicking to enable the search.

Sure for larger volumes search is excellent. would it not be nice in a future version to give the designer the option to enable a scroolbar for lets say 100 records up or down. This would give the best of two worlds.

Always thankful for your insights
Fundi

[admin]

Fundi,

Is there a simple way to suppress this code field?

lookup.png (14.84 KiB) Viewed 8029 times

You could replace pro_code with pro_name and make Code Length wider.

And so pro_name isn't repeated twice in the code and description field, you can set No Description to Yes.

(I think this answers a previous question of your's about lookups being too wide.)

I have a question though - and this is why I didn't understand your original question .

Why even have a code field if you want to hide it from the user?

The only reason a record needs a code field is to make it unique so the user can find it faster.

Steven

[Fundi]

Hi Steven

This is a good idea to swop the fields around and then suppress the description.
I also found that one can change the colour and alignment under style so it looks like a normal field.
The only thing I need to experiment with is the following:

Code: Select all

SELECT id, CONCAT_WS(' ', Firstname, Sur_comp_name) fullname FROM Tbl_Customer

For my drop down I have this sql code. Which allows me to actually display both first and surname in one field and save the respective id. Is there a way to do the same with a Lookup.

I understand the code field now better. Thanks I still hate them. Id numbers, social security numbers, Drivers license numbers etc. I understand if one has a database for a national population that is important or even on a local school as you can have the same Surname and first-name. Luckily my print jobs are unique enough not to work with codes.

Thank you for all your input. It has been very valuable.

[admin]

Johan,

Just put the concat in the Code Field Field of the lookup tab.

Steven