conditional lookup

Fisherman

This is going to be a very generic question, and I do apologize for that.

If I want to make a field's lookup in an existing table dependent upon a new field that I'm adding, does my new field have to precede the existing one in numeric order? I ask because I've added a new field to a table as an option value. The new field is field ID 50000. The existing lookup field is field 1. I'm trying to make the lookup in field 1 dependent on the value of 50000 (and have rearranged the fields in the form to reflect this hierarchy), but the lookup field's behavior still reflects the original table.

It would seem to make logical sense that this would be the case... although it throws a wrench in my design...

DenSter

The field number should not matter in the conditional table relationship. Take a look at the "No." field in the sales line table, and how that conditional table relationship is done. It is on the field level in the table object. Once you introduce the new field, and you give it multiple values, you have to set a condition for all values. Also, make sure that there isn't any C/AL code in the OnLookup trigger of the field, even comments will screw you up.

Fisherman

I'll take a look. That's the idea I was working from - Sales Line and Purchase line.

My issue is the Warehouse Employees table. I'm trying change the lookup on the User column so that we don't have to set up Logins (which fall over to either SQL Server logins or Windows credentials) for people who are doing nothing more than using scanners. The NAS already authenticates against the database at the SQL level using windows auth, and warehouse employees tend to be fairly transient (high turnover), so I'd like to avoid having to set up sql server logins that then have to be managed.

I'm trying to do this without changing the primary key of the table so that the footprint is smaller, so I thought I'd introduce a new option field called "Employee Type" with two values: "Terminal & RF" and "RF Only". For "Terminal & RF" users, the default lookup to Logins is fine, but for "RF Only", I'm trying to create the conditional lookup to a new table I've created called "RF Logins" - table ID 50000.

After your comment, it prompted me to look at the OnLookup() trigger, and there is, in fact, a function call there. I may end up changing the designer lookup to perform the entire thing in code.

DenSter

Yeah I figured that would be the case, and that particular relationship is a funky one . You should still define the table relationship though, so that values that are entered are validated against the right table.

Fisherman

OK - so somewhat related... has anyone ever done this before? I've hit the snag that the RF Login codeunit and the Login miniform both reference the main User table. As soon as I put in my Warehouse Employee code, I get the error that the user doesn't exist ... imagine that .

So... before I go and pretty much recode the login procedure to point to warehouse employee instead of User... anyone have any tips?


**EDIT**

Scratch that...

colingbradley

My need is to check the value in another table to determine if the user is allowed to lookup the associated table from any form.
No selection is possible but the administrator wants to see what the options are.
So far, my SO Line form does not allow any lookup at all, here is my code in Table 36:
SOLVED!!
I added the final part to select the record and it all works fine.
I did not need to select a record so had left that off.

Currency Code - OnLookup()
UserSetup.GET(USERID);
IF UserSetup."Team Filter" = '' THEN BEGIN //Any characters in this field will stop any lookup
LCurr.SETRANGE("Allow Selection in NAV" , TRUE);
IF lcurr.FINDSET THEN BEGIN
LFormCurrencies.SETTABLEVIEW(LCurr);
LFormCurrencies.SETRECORD(LCurr);
LFormCurrencies.LOOKUPMODE(TRUE);
added these lines

IF LFormCurrencies.RUNMODAL = ACTION::LookupOK THEN BEGIN
LFormCurrencies.GETRECORD(LCurr);
"Currency Code" := LCurr.Code;
and all was OK
end;
END;

Fisherman

Accomplished my goal

I had to make more changes than I preferred in order to do so, but the end result does what I wanted it to and still allows for standard methods of authentication.

Now I can give power users the ability to set up Warehouse Employee codes for use on handheld scanners (ADCS) without having to add SQL logins.

What's better for me, as a DBA, is that I don't have to manage SQL logins/permissions for people who typically have a higher level of turnover, and the login that they are given for use on the scanner doesn't give them access to ANYTHING outside of the handheld environment !