Lookups based on Code fields

mbmartinwa

I am trying to modify my forms to show the description of a Code, rather than the code itself. IE, on the customer card, for Payment Terms Code, I want to display the Description from the Payment Terms table rather than to show the Code. How can I do this, but keep the lookup based on the Code field? Any ideas?

Savatage

This too is something I wanted to do but haven't had the time.

Instead we just made our codes more readable

ex codes:
Code - Description
COD - Cash On Delivery
NET30 - Net 30
1%10N30 - 1% 10 Days Net 30
B2B30 - Bill To Bill 30
30|60|90 - 1/3-30 Days 1/3-60Days 1/3-90 Days

etc
etc

Also renaming the code is a snap - it will go through the database & change the record everywhere. Should be a simple code to GET the description from the terms code table.

ajhvdb

In the onformat trigger you can lookup the description yourself and copy it to TEXT.

Tim81

OK, I have a solution for you.

I had a try in my own database and it works.

On the customer card form:
Create a global (e.g. 'PaymentText') Text (50)
Create a global (e.g. 'PaymentTerms') Record (Payment Terms)
Create a new text box and set the SourceExpr Property to PaymentText.

Put the following code in the OnAfterGetCurrRecord Trigger of the form:

PaymentTerms.GET("Payment Terms Code");
PaymentText := PaymentTerms.Description;

Now the Description will be shown in the text box. To get the lookup functionality you put the following code in the OnLookup Trigger of the PaymentText TextBox:

IF FORM.RUNMODAL(0,PaymentTerms) = ACTION::LookupOK THEN BEGIN
  "Payment Terms Code" := PaymentTerms.Code;
  PaymentText:= PaymentTerms.Description; 
END;

If you want, you can delete the Payment Terms Code Text Box on the customer card. If you want to keep it on the form you have to do the first code additional into the OnValidate Trigger of the Payment Terms Code text box to update the 'PaymentText' after changing the Payment Terms Code on this lookup. Have a try.

Tim81

Of course you could add a field like 'Payment Terms Description' to the 'Customer' table. But this would create redundant content. And if you replace the 'Payment Terms Code' field in the 'Customer' table you will have problems with updates. So I think these solutions wouldn't be the best.

Be careful with the above solution: the user can manually fill the PaymentText text box without an error, otherwise nothing will be changed in the database. Next time you call this record the correct payment term will be shown in the text box. To fix this put the following code in the OnValidate trigger of the PaymentText text box:

PaymentTerms.SETRANGE(Description,PaymentText);
IF NOT PaymentTerms.FIND('-') THEN BEGIN
  IF NOT PaymentTerms.GET(PaymentText) THEN
    ERROR('Payment Term does not exist')
  ELSE BEGIN
    "Payment Terms Code" := PaymentText;
    PaymentText := PaymentTerms.Description;
  END;
END ELSE
  "Payment Terms Code" := PaymentTerms.Code;
PaymentTerms.RESET;

Now the user either could type the correct 'Payment Terms Description' (but he has to write it absolutely correct) or he types the 'Payment Terms Code' and all fields and text boxes will be filled correct.

Regards
Tim

mbmartinwa

Tim,

This works perfectly. Thanks for the solution.

Michael

Tim81

You're welcome!

DenSter

I would add description flowfields to the customer table of type lookup into the description of the code. This does NOT result in redundant data. Flowfields are not stored but calculated when they are displayed. The ig advantage is that when you do it this way, you can add the field to any form without a single bit of programming.

If you want to have it display immediately when the code is entered, you put a CALCFIELD("Pmt Terms Description") statement in the Code OnValidate and you're all set.

<edit>of course don't forget to set the new field to not be editable</edit>

Tim81

Yes DenSter this is a possibility. But I thought Michael wanted a field that can replace the Payment Terms Code field (as I took it for granted that Michael knows FlowFields). So to set the new field not editable wouldn't make any sense.

So you have to add the first code anyway. And you wouldn't have the functionality at a users input.

So you would need the last part of code, too. That means, that you only replace the PaymentText var with the FlowField at least.

But Michael, you have to edit the code. I got an error at inserting a new record. So you have to replace the first code with the following (OnAfterGetCurrRecord and OnValidate Trigger):

IF PaymentTerms.GET("Payment Terms Code") THEN
  PaymentText := PaymentTerms.Description
ELSE
  PaymentText := '';

That should work. Have fun!

mbmartinwa

Thanks for the update Tim. And you are correct, I did need a control on the form that replaced the code field altogether, so this is exactly what I needed.

Michael

ajhvdb

Glad you like but did you try my 1-Line solution:

in the onformat trigger add this code:

IF PaymentTerms.GET("Payment Terms Code") THEN 
  Text := PaymentTerms.Description;

That's all, filtering possible