Filtering by non-existent data type

[Vilius]

I have such data set structure:

Requests

req_id	type_id	req_name
1	1	Request Nr 1.
2		Request Nr. 2
3		Request Nr. 3
4	2	Request Nr. 4

Request Types

type_id	type_name
1	Cargo
2	Passenger

Requests and Request Types are joined via Data Manager. Request Nr. 2 and Request Nr. 3  in table Requests doesn't have Request Type set, but it is known internally for our salesmen that all such requests should be considered Passenger requests.

 I want to create a Filter Pane which would allow me to filter Requests table. The Filter Pane itself is not an issue, but I cannot wrap my head around how to do filtering in the table. If I select "Cargo" it is filtering fine, but if I select "Passenger" I only get Request Nr. 4 row. I need to select rows 2, 3 and 4 somehow. Any ideas?

[_ylc]

You can update the type_id filed in your Requests table to link it properly to the request type.

if(len(type_id)=0, 2, type_id) as type_id

This line checks that if the type_id is null then it will autoset the value of type_id to 2.

[Vilius]

Tables come from external database and the data model is done purely via Data Manager. Is there a way to replace the values without converting the model to scripted tables?

[_ylc]

Nope, but you can create a calculated field in the Data Manager.

Go to your Data Manager

Click on the Requests Table and click the pen icon.

You will be taken to this screen, you should rename your type_id field to original_type_id field by double clicking the name.

Click on the Add field then click the Calculated field

fill up the details:

then press Create.

[Vilius]

I'm very sorry, but I've looked at the wrong tables. I cannot do value replacement, because my real data set model is as follows:

Requests

req_id	req_name
1	Request Nr. 1
2	Request Nr. 2
3	Request Nr. 3
4	Request Nr. 4

Request Types

type_id	type_name
1	Cargo
2	Passenger

RequestType Link Table

req_id	type_id
1	1
4	2

There can be Requests without links to type table, and some requests can have both types.