Multi-Layer Relationship

[LWSChad]

Hello Caspio Gurus!

 

I have an action log, and in this action log I have three Foreign Key user fields - Assigned, Creator, Completer. (all three which point at tblUser)

 

How might I go about displaying all these users in one report?

 

I tried creating views (qryUserAssigned, qryUserCreator, & qryUserCompleter) and using those views in a view, but I can only use tables when creating a view.

 

Any help will be greatly appreciated.

CHAD

[Jan]

Hello CHAD,

 

I am afraid, that the best way is to use three tables Users.

You can use Scheduled Tasks to export the Base of three tables and then Import it two times to replace the second and the third tables.

 

As far as I know, many-to-many relations do not work now in Caspio.

 

But maybe there is a better solution.

[LWSChad]

Thanks for your reply!

 

I'll submit a request to add a feature to enable views as sources for views.... IMHO that would be much easier than duplicating the User table every time a new user is added or user info is changed.

[DataCobalt]

I have several (I think over 7 or 8) Many to Many relationships functioning happily within Caspio.

 

The key is to build a third table that only houses the many to many relationship.

 

Granted, this could slow your entry process for Action Logs as it would require three separate entries (though you could automate it pretty quickly to keep the same "feel" of a single form with a few auto-submit forms afterwards) but it would allow you to indicate what users are related to that table and your many-to-many table would need a field to indicate the relationship type.

 

Then you can craft a view that will have (in your case) three rows for each Action Log, each indicating a specific user. Through the use of calculated fields you could easily make a datapage report that would show you all three on a single report.

 

Hope this helps!