+1 vote


We did a customisation on the timesheet a few month ago that allow user to timesheet on opportunity or project.

The customisation work fine, the customer is happy, voilà.


Now, he want to "analyse" the data and make selections with them. Without native SQL, he cannot make a link between the table "timesheet" and "opportunities", by default.

I found the function tag <%SelectJoinTable()%> in dialog/Tables.htm. How can I change things in sys_relations and sys_relentities to have the possibility to link opportunities to Timesheet ?



asked in WorkFlow / Serverscript by (531 points)
Hi Loic, I was suprised to read you have made it possible to timesheet on another table than Projects. I have got some questions in the past to timesheet on cases. I assume your customization can be applied to cases as well then? If so, can you please let me know for which customer you did this customization?
Thanks for your reply.
Br, Jan Krijger
Hello Jan,
I did it for multiple customer, for multiple entity (cases, opportunity, publication, products, ...). But I thing we must take in account the future change in the timesheet (11.3). It seems therefor a bad idea to customise this.

If you and the customer are ready to take the risk of a total non-compatibility in the futures versions, then I can provide you a set of customer for which we did it.


2 Answers

+1 vote

Hello Loïc,

You can add a record in SYS_ RELATIONS that tells the system about the TIMESHEET-OPPORTUNITIES link, assuming that you have added a TIMESHEET.K_ OPPORTUNITY field.

Simply duplicate the existing record for TIMESHEET-PROJECTS (K_ TABLE1=34000, K_ TABLE2=20000), change K_ TABLE2 to 22000 and K_ FIELD1 and K_ FIELD2 to K_ OPPORTUNITY, and put ISCUSTOM to 1.

Restart the COM+ and create a new user session; in the Queries you can now also select the opportunities link from the Timesheet table.

answered by (1.2k points)
edited by
0 votes

Hi Loic

Today I publish a new guide on how to make a stripped-down entity in Efficy without the usage of the Entity Creation Wizard. It also describes how a field K_COMPANY is added to a custom entity named Turnover and being placed in foreign key relation with COMPANIES.K_COMPANY, without the need of a relation table.

If desired, using RULE1='C' in SYS_RELATIONS, you can enforce a delete cascade of Timesheet records when the Opportunity record is removed.

If you want to learn more about SYS tables, the guide shares some basic explanations of the involved system tables.


answered by (7.4k points)
1,246 questions
1,517 answers
328 users