ā Background ā
Iāve created a simple TimeEntries module (using a Studio).
Goal was to be able to create simple time entries related to Accounts or Projects or Project tasks.
For that I created one-to-many relationships from each of these modules to TimeEntries.
I.e: Primary module: Projects, Related Module: TimeEntries
So now I can create a new Time Entry and fill out any of the 3 - Account, Project or Project Task.
Lets say I fill/select only Project for the Time Entry - letās call it Project A.
ā Challenge & question ā
When I create a Report to list all TimeEntries records, how to show Account for the Project that was selected for that Time Entry record?
I can only show the Project because that was directly selected for that Time Entryā¦ or Account that was manually selected for Time Entry.
In Reports, it should somehow get the Account through the Project that is related to the Time Entry.
I donāt know much about reports but if you need to work around that limitation, I can try a suggestionā¦
If I understand correctly, your problem is that the Project is not related to the Account, so when you are in the project you cannot know the Account. But in your mind, the two are related. You need to represent this in SuiteCRM.
Maybe you can create that extra field in projects, to keep track of the Account. Then you would have a way to search, although getting what you need in the report might still prove difficult.
Can you explain better how Projects are related to Accounts in your mind? Is it one-to-one?
If so, why do you include both in the time-entry? Wouldnāt one of them be enough?
Thank you very much for taking the time to go through and think with me.
In SuiteCRM, Projects are related to the Account (thatās how it was by default and I havenāt changed it)
I guess the core challenge here is that I donāt know how to get the Account relationship from the Project when creating the Report.
Even though the project has an account, Reports doesnāt seem to āseeā/āreachā that account. Report view, where the last entry is missing the Account: Link to the image
āIf so, why do you include both in the time-entry? Wouldnāt one of them be enough?ā
Both are included in the time-entry since time entry can be created for a specific project or sometimes just for an account.
Project will be selected if the time-entry is related to the project for Client A.
Account (Client A) will be selected if the time-entry is related to some work that is not exactly related to some project but should still be related to that client.
The wish for outcome is to get a report that will summarize/show all time-entries for accounts.
Maybe you can try designing the relationship between the Time Entry and the other modules as a āFlex Relateā. Search for āSugarCRM 6.5 flex relateā to learn about it.
Thanks, did some reading. Does not seem to solve it since one-way connection is made. Unless during the saving, it automagically autofills the Account value.
Or what idea you had with it?
But reading about Flex Relate and surfing around in Sugar docs led me to something called āOptional Related Modulesā, which I was unable to find in SuiteCRM Reports. Although that seems to be the key: Define āAccountā as an optional relationship and then it tries to load it through Project.
Is it possible to create a report with raw SQL query? In database itās so simple: Time Entry table has Project ID -> project_accounts table has an account_id field and accounts table has the required name. Some SQL joins and job done.
I am just trying to give suggestions for explorations, please understand that I am not a specialist in reports, or in Projects, for that matterā¦
Are you sure you canāt get the report to produce what you want?
I think reports are able to traverse tables through joins, like you want. In the tree I think you can go from your module, on to the related Project, and then on to the related Account.
But maybe Iām missing something. Maybe you can try first with a standard module (non custom), like Project Tasks, to see if you can build a similar report.