Select non primary key column when using table relation

GavNav

Hi all,

I have to redesign form 42 (Sales Order) for a long established customer using v5.01 SQL. One of the mods involves a bespoke field called "Order Given By". Currently it links to the Salesperson/Purchaser table and the user selects the appropriate record. Naturally this works but the problem is that it populates the cell with the Primary Key value (Code). The user would prefer if the Name in the record was displayed. Has anyone got any idea how to do this. The is a look up table relation, user clicks their chosen record and the textbox displays the Name field (non pk). Before it is suggested I don't want to add Name to my PK because it may mess up a lot of GET statements which I have. Any advice welcome.

Thanks

Gav

geordie

I think there are two solution:
- Remove table relation and customize OnValidate/OnLookup table trigger to rescue the name and check that is matching with the name from Salesperson/Purchaser table.
- Keep the current logic and adding a new field on Sales Order table as a FlowField of type lookup to pointed to field Name (if users accept to chose the salep. code and see the name in an adjacent field).

GavNav

Thanks for your reply Daniele. In the end what I did was created a new textbox on the form and set that to a variable "SalespersonName". In the OnValidate of my bespoke table relation field "Order Given By" I added some code to GET the record from the Salesperson/Purchaser table and set my new textbox equal to Name from the retrieved record. It works perfectly. Thank you for your suggestions.

Gav