Accesing a field through a complex join or view

[ereyes]

Hi
Hope you can help me.

I have three tables: Inventory, Companies and Invoices

Inventory fields

1. Id_Inventory (key)
2. Item
3. Id_Invoice (Invoices databasejoin - Label: Invoice Number )

Companies fields

1. Id_Company (key)
2. Company name

Invoices fields

1. Id_Invoice (key)
2. Invoice Number
3. Id_Company (Company databasejoin - Label: Company name)

This is the problem:

I also need to show the name of the company in the inventory table -> 4. Company Name
The problem is that from the inventory table I can only access the id of the company through the invoice table.
How I can access the name of the company?

Inventory table -----> Invoices table <--------> Companies table

Thank you very much for your help.

 

[troester]

Use "Or Concat label" to fetch the company name
http://fabrikar.com/forums/index.php?wiki/database-join-element/#or-concat-label-examples

 

[ereyes]

Ok, I should use the Concat label.
There is no problem if I want to access the invoice number (Invoices table). There is a databasejoin pointing to it.
But I cannot access Company Name (Companies Table).
Remember. I need to access to the company name from Inventory Table.
I need to show the invoice and the company which the invoice belongs of each item of the inventory table

Inventory ---> Invoices ----> Companies.

I specify en Concat label the field Invoices.Invoice_Number.
But cannot access the field Companies.Company_name
It says the field doesn`t exist.

I tried with

Invoices.Invoice_Numbre , ' - ' , Companies.Company_name

But also tried with placeholders.
I`m missing something but I don't know what it is ....

 

[troester]

Yes, as in the WIKI you have to fetch it via a SQL query

If the label of your dbjoin is a dbjoin value itself get it's label with
Code (Text):
(SELECT name FROM table2 WHERE table2.id = {thistable}.element)

So something like
{thistable}.Invoice_Numbre , ' - ' ,(SELECT Company_name FROM Companies WHERE Companies.Id_Company = {thistable}.Id_Company)

You better use {thistable} (literally) instead of Invoices because Fabrik may create aliases like Invoices_1 etc

 

[ereyes]

Perfect !!!

That works !

Troester ... thank you very much for yout help !