Using lookup in a lookup

I am wondering if it is possible to look-up information in a table through information in an intermediate table. For instance, I need information from the tasks table that has linked fields with the projects table. The table I am working from has only a link to the projects table. I try the lookup inside the lookup function but that does not work. Can anyone help.

It sounds like you’re still thinking in a typical relational db mode where links have to have indexes and adding fields is a big deal. Why not give the current db a field that can be used to do your lookup? It can be constructed in seconds and
filled with the needed values almost as quickly.

There’s no reason that I know of why a lookup( function cannot be nested in another lookup( function, other than it might be somewhat slow if the databases are large. So probably there is an error in your formula. Please submit the exact formula you are trying to use, the names of the fields in each database (screen shots would be good if possible), and the exact result you are getting from your formula.

From the database Factuurregels (I am Dutch) I want to get the activity description of an activity which is store in the Aktiviteiten database. I can access this data through the database Facturen and Projectenlijst.

For now I have made two additional fields in the Factuurregels database, Klant and Project, that lookup for the information from the Klanten database and the from the Projectenlijst database. With the Project field I can lookup the information I need from the Aktiviteiten database.

Lookup functions for the fields:
Klant: lookup(“Facturen”,«Factuurnummer»,«Factuurnummer»,«Klantreferentie»)
Project: lookup(“Projectenlijst”,«Klantreferentie»,«Klant»,«Projectnaam»,"")

Final information I am looking for:
Omschrijving: superlookup(“Activiteiten”,|||««Project»» = Project and ««Activiteit»» = Activiteit|||,|||Omschrijving|||,“default”,""))

I tried to nest the above two in the last super lookup but this did not work, see below. I would be very thankful for anybody to show me how to nests these two first lookups in the last superlookup and the syntax that is needed.

I tried:
superlookup(“Activiteiten”,|||lookup(“Projectenlijst”,«Klantreferentie»,lookup(“Facturen”,«Factuurnummer»,«Factuurnummer»,«Klantreferentie»),«Projectnaam»,"") = Project and ««Activiteit»» = Activiteit|||,|||Omschrijving|||,“default”,""))

The error code I got was: Execute code contains syntax error: Missing Left Parenthesis.

Thank you, Nico

This formula is incorrect. There should only be one ) on the end, not two. ( and ) must always be balanced. The error message tells you exactly what is wrong (you could also fix this by adding a ( to the very beginning of the formula, but it is simpler to leave both off).

The final formula has the same problem, just take the final ) off and the error message will go away. Based on the information you’ve given I can’t say for sure that the formula will work correctly, but that will fix the “Missing Left Parenthesis” error.

How are you using this formula? If it is in a procedure, I would suggest splitting this up into separate formulas, assigning the intermediate results to a variable. That won’t change the way that Panorama works, but it would be easier to follow and debug. In fact, even if you are using this in a way that it can’t be split up (perhaps in a Text Display object) I would suggest first creating the parts separately in a procedure, and testing each part, then put it together. I wouldn’t transfer it to the Text Display object (or whatever) until I had verified that everything was working. I’ve used this technique frequently with complicated formulas.

Hi Jim,

Thank you for your support. It works now, the bracket at the end came from copying. I also needed to put the field ««Factuurnumber»» in double chevrons instead of single. Currently I use this lookup in a calculated field. But I will try your suggestion to to put it directly in a text display object where I need it. As I am new to Panorama can you give me an example of how to use variables in a text display object? Thank you.

Best regards, Nico

The first section of this page explains how to set up a formula for display:

For a formula like this one, there might be an advantage in the way you are doing it now, with a calculation on the field. The Text Display object will have to perform the lookups over and over again, and depending on database size that may take some time. What you are doing is essentially performing the lookup in advance, only when the data it depends on changes. So that might be faster. On the other hand, putting the formula in the Text Display object guarantees that the lookup is “fresh”, with the latest data from the databases it is looking up from. You’ll have to decide which is best for you.

You mention “calculated field”, which is a terminology that FileMaker uses, so perhaps you have FileMaker experience. Panorama doesn’t use this terminology, and I think it works a bit differently than FileMaker. When you do this in Panorama, it runs the formula and then stores the result in the database. It’s actually taking up space in the database. I’m not a FileMaker expert, but I think that FileMaker doesn’t store the result, it just calculates it when needed. So that’s more like putting a formula in a Panorama Text Display object, which also performs the calculation when needed for display.

In FileMaker I think you have to set up a “calculated field” for any formula you want to use. Panorama doesn’t require that. You can put a formula pretty much anywhere a value is needed – in a graphic object, in programming code, etc. You don’t have to define that formula in advance, you just type it in where you need it.

Try removing the last parentheses.

There must be an equal number of right and left parentheses.