Easy List Report with a twist

[ColBasicuser]

I want to create a simple list report, however data selection is giving me a headache 

Scenario - 1) We have tours and guides (separate tables)  2) A guide may work on several occasions on a tour   3) I want the list report to show details of the guides attending  a selected  tour (easy so far!)  4) On my list report of guide's details I only want guides to appear if they work on the  selected tour and  only appear once  regardless of how many  times they work on that tour,  but just cant  get this to work; I wind up with the guide details for the same guide appearing  many times, 

Tour Table 

Tour_Id, Date, Guide_Id, Other_related_tour_ info

Guide Table

Guide_id, Guide_Name , Guide_email, Guide_image_Id, Etc

Seems stupidly simple, but turns out to be very tricky

Your comments/suggestions  received with thanks

PS.  I am only a very basic user and can't write (or understand) script)

Thanks

[TellMeWhy]

Hi @ColBasicuser,

Can we elaborate on your workflow?

You have two tables

TOURS and GUIDES

 

you have List of TOURS, when CLICKED, you will have List of GUIDES that CAN attend on that tour

When you click on the GUIDES that are attending that SPECIFIC TOUR,  you want to see the specific guide's details (?)  

Is this workflow correct or I'm missing some details?

 

Do you have specific set of tours as well? if yes, you might want to put it on a table, and another table of Tour schedules. this means you'll have 3 separate tables but the tour tables is for lookup only to be set on tour schedules where it stores the tour and the guides that will attend the tour on that schedule

 

you might want to check this out as well https://howto.caspio.com/tables-and-views/relationships/

[CoopperBackpack]

Hello @ColBasicuser,

If I got the workflow correct, you want to display the distinct guides on the DataPage.

As far as I know, this is really tricky. On the Tabular Report DataPage you may use a Grouping option, however it does not allow to avoid displaying duplicates, but just group them.

I believe, that the solution is to create a new table like Distinct_Guides and populate it by a Task or a Triggered Action (if these options are available in your plan).

Task can be used for the records that already exist in the Tour Table. When the new data is inserted to the Tour Table the Trigger will be helpful. 

Here are the articles: 

https://howto.caspio.com/tasks/

 https://howto.caspio.com/tables-and-views/triggered-actions/

The Task example:

 

[CoopperBackpack]

As for the Triggered Action, you may try the following approach:

[CoopperBackpack]

So, the idea is to use the Distinct Guides table as a data source of the DataPage.

Since you need to display detailed information about the guides, you may consider to use a View as data source instead of the table.

Views allows to join tables:

And, selecting the required fields from the View, you can build the Report DataPage.

Hope this helps. 

 

[ColBasicuser]

13 hours ago, CoopperBackpack said:

So, the idea is to use the Distinct Guides table as a data source of the DataPage.

Since you need to display detailed information about the guides, you may consider to use a View as data source instead of the table.

Views allows to join tables:

And, selecting the required fields from the View, you can build the Report DataPage.

Hope this helps. 

 

CooperBackpack  this looks like a good possible solution to me. So, when the Ops team adds a guide to a tour a trigger can add the guide to the 'Distinct_Guide-Id'; Gets a bit complex when  the Ops team removes or changes a guide allocated on a tour, especially  when the guide has been scheduled say 3 times on the tour and the ops team wants to change one of those occasions for a different guide. 

Appreciate the thought starter any more Ideas will be very welcome

Thank you.