Masking fields

jeffb_r4 · 2007-11-06

Hi all.
I have a situation where we have item numbers like so.
12-34-5678.
The customer would like them stored in the database as 12345678, but displayed (on item cards, invoices, etc.) as 12-34-5678, if possible.
If necessary, they could be displayed internally as 12345678 but show up on invoices as 12-34-5678.
I have been told that this can't be done.
Does anybody disagree, and if so, how can it be done?
Thanks.

Jut · 2007-11-06

Technically the request could be implemented. E.g. you could create a new field in all involved tables for the separated item no. this field could automatically be filled when the "real" item no. will be validated. in all reports and form you could display the new no without changing the real posting-functionality.

However, you would have to customize many tables, forms and reports, thus the effort is absolutely not small.

themave · 2007-11-06

we do this exact thing, using the no2 field in the item table

it stores the stripped number, we have a part for example 5-4-37

it is stored in the no2 field as 5437

then I can put the appropriate field on the reports that need it.

We also set up an item cross refernce for both numbers

5437 and 5-4-37 so that we can look up the part using either number on sales orders, purchase orders ect. so we enter the cross reference field on the sales order and it brings up the appopriate part number

I added a key to the item table for the no2 field. so user can set there own preference on how they look up numbers, on the item list if they like to use the no2 field sort by that and can quickly search on it. and other who like the whole number can use the regular no. field.

In our case, we print out the whole part number, so we didn't change any reports, and no special programming was needed. since the no2 field is a standard field, just doesn't show up on the form by default.

Savatage · 2007-11-06

We use No.2 too - even have the AltSearchField of the "No." field in the item table changed to No. 2 so we could enter either one in orders.

jeffb_r4 · 2007-11-06

Thanks for the suggestions.
In using the No2 field, does that mean whenever you set up a new item, you need to enter the No, the No2 and the cross-reference, or do you have some way of automating this?

themave · 2007-11-06

jeffb_r4 wrote:

Thanks for the suggestions.
In using the No2 field, does that mean whenever you set up a new item, you need to enter the No, the No2 and the cross-reference, or do you have some way of automating this?

If you have access to cal code in tables or forms you can set code to create the concentrated no2 field.

I don't have access to the cal code on tables or forms, so I use a non-printing report that filters on the item table and a blank no2 field. it runs and creates the cross references, and another report creates the no2 field. I run it from the job scheduler. so users think it is automatic.

you can use something like this to strip the characters you don't want out

STR:=DELCHR(Item.no,'=','/','-');

other users here can probably give you the exact code to use.

Savatage · 2007-11-06

If it will always be all Numbers else you can add the alphabet

OnInsert or the item table

"No. 2" := DELCHR("No.",'=',DELCHR("No.",'=',0123456789));

or

"No. 2" := DELCHR("No.",'=','-');

Also to auto add into a different table. This is how we add UPC Codes scanned into the item card to an associated table called AltUPC table Using INSERT.

IF AltUPC.FIND('-') THEN
AltUPC."Item No." := "No.";
AltUPC.Description := Description;
AltUPC."Description 2" := "Description 2";
AltUPC."Item UPC/EAN Number" := "Item UPC/EAN Number";
AltUPC."Date Entered" := WORKDATE;
AltUPC.INSERT;

In your case you will fill in the fields needed in the Cross Reference Table and put the code on the OnValidate of the item table.

Masking fields

Comments

Categories