dbjoin Concat Issue

[bespokeappstt]

I have a list called Rental and it has a dbjoin element. The dbjoin is pulling data from the NonSerialAssets list. I have chosen to use the 'Concat label' option and I am concatenating the columns:
nonserialasset
vendor
quanitity

There is only one problem. 'vendor' column in the NonSerialAssets list is a dbjoin on the vendors list. As a result for the concat on the Rental, the vendor id is showing. I want the vendor name alone to show. What should I do?


1, 2 and 3 are all vendor id's from the 'vendors' list. How can I get the actual name to show and not the id?

 

[troester]

http://fabrikar.com/forums/index.php?wiki/database-join-element/#or-concat-label-examples
so something like
(SELECT name FROM vendors WHERE vendors.id = {thistable}.vendor)

 

[bespokeappstt]

Trying to figure out what's wrong with my syntax. The NonSerialAssets list looks like:


The element that I doing this dbjoin concat is accessory_non_serial

My concat statement is:

(SELECT vendor FROM rad_nonserialassets WHERE rad_nonserialassets.id = {thistable}.element)

What am I missing? Just in case, this is what nonserialassets look like:

 

[bespokeappstt]

Realized some syntax errors and changed it to:

(SELECT vendor FROM rad_nonserialassets WHERE rad_nonserialassets.id = {thistable}.accessory_non_serial)

Now the page doesn't even load. I get a


Any ideas?

 

[juuser]

Didn't dig in to the whole thread, but you are selecting "vendor" from "rad_nonserialassets" which is a dbjoin element. If you want the "name" of the vendor, you should select the name field from the Vendors table where the vendor dbjoin element refers to.

 

[bespokeappstt]

The column 'vendor' is the vendor name. It's best practice to use the 'id', but for this case, should I use 'vendor' instead?

 

[bespokeappstt]

When I use this I get only the 'id' for the row in the rad_nonserialassets table.


This is what the above config displays:


Only the row id in the rad_nonserialassets shows. This is expected as that element is a dbjoin itself. For concating dbjoins the following is supposed to work:


When I do that, the error is huge. The page doesn't even load.

 

[bespokeappstt]

The page breaks with an Internal 500 error:

 

[juuser]

Still, "vendor" in rad_nonserialassets table is a dbjoin element, so you will get the id when you use it in concat label. To which table does this vendor dbjoin element refer to? You should refer the concat label to this table's vendor name element.

And in your previous post's concat label you have ... = {thistable}.element. Is this just an example or does the element called "element" really exists?

 

[bespokeappstt]

I thought {thistable}.element was the syntax. Okay. So the original table is called 'vendors'. Looks like:


The nonserialassets list is a dbjoin on vendor in the vendors list. And I want to pull the vendor name(vendor column) data into the rad_nonserial_rentals list. I modified the concat string to:

(SELECT vendor FROM rad_vendors WHERE rad_vendors.id = {rad_nonserial_rentals}.accessory)

I am using the 'accessory' element in the the rad_nonserial_rentals, which looks like:

 

[juuser]

The accessory part doesn't make any sense for me, i think it should be:
(SELECT vendor FROM rad_vendors WHERE rad_vendors.id = {thistable}.vendor)

so basically the same as troester suggested above.

 

[bespokeappstt]

Okay, yes, that works. The concat string ended up being:

nonserialasset, ' ', (SELECT vendor FROM rad_vendors WHERE rad_vendors.id = {thistable}.vendor)

[solved]