TeamDesk Knowledge Base & Support

Slava Shinderov  Staff  2/16/2015 2:22:54 AM

@Andrew I guess, you're talking about some kind of union between tables.
It will be helpful if you'll be able to describe practical usage scenario or what kind of problem you're trying to solve with that view.

Andrew Winters 2/16/2015 1:34:49 PM

@Slava - Here is my scenario: I have a "Contacts" table that is master to multiple other tables -- "Phone Calls," "Events," "Documents," etc. This works well as the user can open a contact and view all the activity per that contact by type. However, each record in these slave tables can also be a billing event. I would like the user to be able to create a bill with a view that selects a contact and a time period. This view will list each billable event by contact all mixed together so that it can be sorted by date, and modified right from that screen. It can then be printed or emailed to the client as a bill with the total time spent during the inputted time period.

I have experimented with a couple of alternatives but none have been entirely satisfactory. One is to lump all the slave tables into only one table and choose type as a column. Form behavior will then open only the fields relevant to that type. This is clunky though and requires the user to take extra time with every new record inputting the type and waiting for the form behavior to load. The other alternative, that I am working on now, is to leave the slave tables in place and add another table called "Billing." Any record opened in one table will use rules to open a "Billing" entry that will be a slave to the original record, using lookup to transmit the information. I now will have all the information in one table and can view the altogether. The downside, though, is the records is this new table can't be edited directly. So if the user is a preparing a bill, and wants to make a tweak, she has to work back to the original record to make the change.

Hopefully this is clear. Any comments are appreciated.

Slava Shinderov  Staff  2/16/2015 3:13:40 PM

@Andrew I'd recommend you to use single table for all linked details such as "Phone Calls", "Events", "Documents", etc.
There are some tricks which will allow you to improve its usability.
First of all, for each activity type add a dummy formula column in "Contact" table which will return corresponding activity type.
You'll use these columns in match condition of many-to-many relations:
http://www.teamdesk.net/help/8.2.aspx
After that you can create a separate many-to-many relation for each activity type with following match condition:
Contact [Id] = Activity [Contact]
Contact [Phone Call Type] = Activity [Type]
As a result, you can specify corresponding details view for each type of activity and, the best thing, "New" button on each details view will automatically fill activity type with corresponding value, so user won’t need to do it manually and behavior will work automatically.

Andrew Winters 2/16/2015 7:51:05 PM

@ Slava - I was able to replicate - so that's handy being able to call a new specific type directly from the detail view. However, one thing I don't think the user will be able to do is call the specific type from the "Add New" function that is visible at the header of the database from virtually every page. If a new Activity is entered from that tool, which in my experience is often, then the user will have to select the type and wait for the form behavior. Maybe not as burdensome as I'm making it out but not quite as handy as when the Activity is subdivided into multiple table.

Slava Shinderov  Staff  2/17/2015 2:26:35 AM

@Andrew as a workaround you can remove 'New Activity' from 'Add New' at the header by hiding "Activity" tab using "User Interface" section of table properties.

Additionally it's possible to create Formula-URL buttons within "Contact" table to quickly create corresponding activity type:

URLRoot() & "/edit.aspx?t=<table>&fid_<refcolumnid>=" & URLEncode([Id]) "&fid_<typecolumnid>=" & URLEncode(<typevalue>) & "&back=" & BackURL()

Please replace
<table> with "Activity" table id
<refcolumnid> with Activity table reference to "Contact" column id (you can see it in particular column setup URL),
[Id] with "Conatct" table key column name, if different,
<typecolumnid> with Activity table "Type" column id (you can see it in particular column setup URL),
<typevalue> with activity type value.