Looking up at two different tables?

[Helder Luis]

Hi, fresh user here...

Just wanted to know if it't possible to have a lookup "looking" at 2 different things...

I have an invoice table and a budget table (and a products table)... I want to be able to use a yes or no field to select if i want to populate the "QT", "Description", "Value", etc. fields with data from the budget or fresh new one form the prducts table, this way creating a new invoice without a budget...

I used lookups fields and they work with the budget, but now i can't find a way to move form budget to products table...

Budget table aslo acesses the products table.

Thank you all...

[CobaltSky]

Hello Helder,

I suggest that instead of using a conventional auto-enter lookup, you instead define the field as an auto-enter calculation. Then you will be able to use a pair of Lookup( ) functions, enclosed within an If( ) function, so that one or the other Lookup operates, depending on the condition you specify for the If( ) function. Eg:

If(YourField = "Yes"; Lookup(Budget::Value); Lookup(Products::Price))

That will invoke one or other lookup, depending on the value in the YourField field.

Note that you will want to turn off the options for 'Do not evaluate if all referenced fields are empty' (at the bottom left of the calc definition dialog) and 'Do not replace existing value of field (if any)' (in the field options Auto-Enter tab) in order to get the values to populate and refresh in a way that is similar to a conventional lookup.

[Søren Dyhr]

Can anyone explain to me why it has to be Lookups in the first place or calcfields for that matter since showing a value more than a relation away shouldn't include calc's but perhaps instead an extra TO?

--sd

[Helder Luis]

Hi, Ray, i can't make it happen the way you explained, how do i define the relationship this way, between the tables?

In a simple lookup it's easy, but i can't figure it how this way...

And what are you sugesting S

[Søren Dyhr]

Rays suggestion is correct, but the use of Lookups kind of defeat the relational structure, unless the prices changes over time, so the older the invoice is the less is charged pr. item. I was wrong of me to bring it up here... Since we're dealing with invoices stick with Rays suggestion, but in other matters of more live nature employ the different from and equal relationship with the keyfieldvalue "Yes"

--sd

[CobaltSky]

Hello Helder,

The set-up I described provides a pre-programmed choice between two separate lookups, one using a relationship to a TO called Budget and the other using a relationship to a TO called Products. Which lookup is invoked depends on the value in the YourField field.

So each of the relationships, in themselves, should be set up as you would do for a conventional lookup. The calculation will then source the data from the appropriate TO depending on the state of the record when the lookup occurs.

[Helder Luis]

In my case i have 2 tables (budgets and invoices) getting data from one other table (the products table), but i want to override that in the invoices in the case of having allready a budget done for that invoice...

So the invoice would lookup at the budget insted of the products table...

Is this what you explain me?

Thank you...

[Helder Luis]

I was thinking... i can do this by creating a script in budget that creates a record in invoices and populates the fields in invoices from the fields in budget...

Do you think this is doable?

Thanks...

[CobaltSky]

I was thinking... i can do this by creating a script in budget that creates a record in invoices and populates the fields in invoices from the fields in budget...

Do you think this is doable?

Thanks...

Hmmn... I was under the impression from your earlier comments, that these may have been tables within the same file - in which case they will not have separate scripts as such, but would require a script which switched to the appropriate layout to work from there.

That aside, yes, it may be doable - but it does not, of itself, necessarily provide a solution to what I understood to be your central problem, which is that you may need to source some data from the Products table rather than the Budget table?!