Help needed for table with multiple (equal) id's

[Tom16]

Hi,

I'm stuck with the following: I need to create a list with a table that has multiple "request_id" as the common denominator. I have no idea how to make a selection to get this listed.
Any help is greatly appreciated.

Tom

 

[georgie]

Hello

It depends, what about "request_id"? What is it? Where is it stored or where will it be stored?

 

[Tom16]

Hi Georgie,

Thanks for your reply.

I'm using a component in Joomla for appointments. (ABpro4) wich uses User Defined Fields.
The UDF's are the udf_values in the table. The id field is incremental and not related. Only the request_id is related to the appointment.
As you can see, the udf_id is equal to the field(name) and request_id to the form.
What I need is a list where, for instance,

10 = Lastname: Lastname2
8 = Firstname = Firstname2
5 = City: City2
....and so on.

I have no idea how to do that in Fabrik.

Tom

 

[georgie]

Humm maybe a databasejoin element on "request_id" field, which would point on the "request" table (Does this table exist in your system?).
Begin to create a list with your main table (the table that interest you), this in a test website.

 

[cheesegrits]

Unfortunately, there isn't really a way to handle that kind of user defined field from another application in Fabrik.

The only way I could think of would be kind of long winded, and require knowing exactly what fields there are and what their 'udf_id' is, and build a MySQL view to convert the other table into a single row. Basically a "pivot". However, this would be a read-only table ... being a view, you can't write to it. But if all you need to do is read, then something like ...

CREATE VIEW calendar_view
SELECT request_id,
     MAX(CASE WHEN udf_id='10'  THEN udf_value END) lastname,
     MAX(CASE WHEN udf_id='8'  THEN udf_value END) firstname,
     MAX(CASE WHEN udf_id='5' THEN udf_value END) city
FROM your_calendar_table
GROUP BY request_id

That would create a "view" which pivots the other table into rows, so ...

udf_id,request_id,udf_value
10,123,Messenger
8,123,Hugh
5,123,Huntsville
10,124,Lennon
8,124,John
5,124,New York

... would become ...

request_id,lastname,firstname, city
123,Messenger,Hugh,Huntsville
124,Lennon,John,New York

You can then create a Fabrik list using that view ... but as I said, you wouldn't be able to edit or add to it.

-- hugh

 

[Tom16]

Hi,

What I have at the moment is a query that works fine in Navicat.

SELECT
udf_value
FROM j30_sv_apptpro3_udfvalues
WHERE udf_id =10

Where 10 is the lastname, 5 city, 4 gender etcetera...

The result is:

From this table where request_id is the common id:

udf_id request_id udf_value
10 12 Achternaam
5 12 Plaats
11 12 Plaats
4 12 (d)Vrouw
8 12 Voornaam
2 12 Adres
6 12 Postcode
7 12 Telefoon
10 13 Lastname
5 13 City
4 13 (d)Vrouw
8 13 First name
2 13 Address
6 13 Zip code
7 13 Phone
10 14 Lastname 2

My question is:

I am using databasejoin as this the only plugin where I can insert a query.
The query is accepted but returns no result.
Is this the right plugin or is there an other way of doing this?

Any help is much appreciated.

Tom

 

[cheesegrits]

What I meant was, create the query I gave you as a view. So in Navicat, create a view, and paste in the query (without the "create view" bit) and save it.

Then you'll be able to either create a Fabrik list on that view (as if it were a table), or join to it with a join element.

-- hugh