2

I can't get simple DbLookup formula working with a variable. I have a view with employees and their managers, i get unique managers' records with DbColumn and then i need a list of people managed by this person

pms=Evaluate({@Unique(@Dbcolumn("":"";"":"";"admin";3))})

ForAll pm In pms
   result = Evaluate({@DBlookup("":"";"":"";"admin";} & pm & {;1)})

this doesn't work, i have also tried using vertical bars and additional quotation marks around pm but i keep getting either type mismatch or execution failed errors

result = Evaluate({@DBlookup("":"";"":"";"admin";} & "keyword" & {;1)})

this works fine

Qarlog
  • 23
  • 1
  • 3

1 Answers1

3

You have to enclose the value of pm in quotation marks too:

result = Evaluate({@DBlookup("":"";"":"";"admin";"} & pm & {";1)})

This way it is recognized as a string.

Example:

If pm has a string value "Domino" then Evaluate string has to look like this:

@DBlookup("":"";"":"";"admin";"Domino";1)

but in your original formula version it would be 

@DBlookup("":"";"":"";"admin";Domino;1)

BTW, the code would break if pm would contain a quotation mark. If you are sure that can't happen then the code is fine.

Knut Herrmann
  • 30,880
  • 4
  • 31
  • 67
  • i have tried it before and tried it now again - type mismatch, CStr(pm) doesn't help either. there's no way for pm to contain a quotation mark – Qarlog Jan 07 '14 at 21:05
  • You get a type mismatch if you handle `result` like a string but it is an array. Use `result(0)` instead e.g. `print(result(0))`. – Knut Herrmann Jan 07 '14 at 22:30
  • i don't. the result array is empty every time i get an error ( isEmpty(result) returns True) and the only way i use it is in another ForAll loop to pick single records and create a separate list so the issue is still within Evaluate :) – Qarlog Jan 07 '14 at 22:37
  • You look in first column for value of pm and return first column - does that make sense? @DbLookup always looks for key in first sorted column (probably not the third column with managers) – Knut Herrmann Jan 07 '14 at 22:45
  • and that's a score! i'm almost sure i took it under consideration before but it seems that i forgot : ) thanks a lot! – Qarlog Jan 07 '14 at 22:51
  • I guess you need a resorted view: first sorted column with managers and second column with managed people. – Knut Herrmann Jan 07 '14 at 22:57