Portal and filters

[MassimoA]

Hello,

i have created a new folder based on the 'portal' schema, inside my company folder, and i called it 'Riepilogo'

I have created in this 'Riepilogo' folder two asset, the first with an explicit filter:

http://<myIP>/sgs/bin/index.php?folder=^tasks_<user>&view=display3&filters=company_ext|like|1test

the second with a relative filter:

http://<MyIP>/sgs/bin/index.php?folder=^tasks_<user>&view=display3&filters=company_ext|like|@companyid@

After that i have modified my companies.xml placed in the custom folder:

...

<!-- to work have to be present in the view (display, details, etc) the field (and the value) companyid -->

<singlebutton views="details" name="link_portal" displayname="Portale" onclick="locate('index.php?folder=206320&amp;view=display&amp;filters=company_ext|like|@companyid@');" icon="all.gif" />

...

After that i select the asset with companyid=1test in detail view and click on the button 'Portale'

The 'Riepilogo' was displayed, the first asset with the explicit filter show correctly the asset i look for ( i use it only to prove it exist) but the second 'Riepilogo''s asset is empty.

what is the correct way to show a relative filter?

In attach the screenshots

Massimo

[Patrick Pliessnig]

Hi Massimo

I think the portal schema executes just the string in the url field without modifying it. The way you like to achieve this with portal schema doesn't seem to be a good way for me because this would need two decodings, first from locate and then from url.

From what I can see, you like to display user tasks for a certain company.

So, why don't you just create a view Riepilogo based on a nodb_ view that combines tasks and companies within a single SQL Statement?

Similar to this one for example which combines :

bin/modules/schema_sys/nodb_calendar_contacts.xml

What do you think?
Patrick

[Massimo Arnaudo]

Hi Patrick,

thanks for your answer, always appreciate!

The portal schema is important because it permit to view in a single report different groups of informations joined to a single asset (appointment, tasks, problems, email, etc).

Your suggestion is interesting and i will study it.

For testing i have created a new sub folder based on the nodb_calendar_contacts.xml file but it result empty although i have the birthday date for a contact on 29/09/1971... (see screen shot)

Massimo

--
You received this message because you are subscribed to the Google Groups "Simple Groupware" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupware+unsubscribe@googlegroups.com.
To post to this group, send email to simple-groupware@googlegroups.com.
Visit this group at https://groups.google.com/group/simple-groupware.
For more options, visit https://groups.google.com/d/optout.

[Patrick Pliessnig]

I guess your new test folder is empty because there is no asset attached to this folder and/or you didn't deactivate the folder filter within the query (see the nosqlfolder="true" tag for this).

For testing i have created a new sub folder based on the nodb_calendar_contacts.xml file but it result empty although i have the birthday date for a contact on 29/09/1971... (see screen shot)

To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.

[Patrick Pliessnig]

From your description of the use of sgs I deduce that in standard usage of sgs you see a company rather than a project without end date. in this structure a portal folder would be the top folder of a sub-folder system (like in personal folders) and contain absolute url-strings rather than relative url-strings. you could then merge the different company task lists (or other company related lists) within single personal company task lists for each collaborator (with the folder merge operation). another option would maybe be to use the personal folder portal for each collaborator to achieve the effect you like. in other words the need for relative filters arises because you want dynamic portals (dynamic urls) instead of static portals (static urls).

The portal schema is important because it permit to view in a single report different groups of informations joined to a single asset (appointment, tasks, problems, email, etc).

To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.

[Patrick Pliessnig]

I understand that you want to display other assets related to a company asset. nodb_schemas allows you to create views with custom sql-statements within your company folder. let's say that you want to display all tasks in a tasks-view within the company folder. you could create a nodb_company_tasks.xml schema (like the nodb_calendar_contacts.xml) in the custom schema folder.

this nodb_company_tasks schema would contain a sql-statement similar to this one:

custom_name="(select c.*, t.subject, t.begin, t.ending from simple_companies c, simple_tasks t where c.companyid=t.companyid) p"
(I didn't test this particular sql-statement. It may contain errors)
(this kind of sql-statements just extend the normal assets with other calculated fields. for each field you like to display you need to add a field-tag to allow sgs to handle it properly. the first view-tag in the nodb_schema is taken to display this extended schema)

In the companies schema you then add this view to display the company_tasks schema:

<view name="tasks" displayname="compitos" schema="nodb_company_tasks">
</view>

As a result you would have a view-tab named "compitos". You could 1) select a company 2) click on compitos and have all the tasks related to this company displayed.

The portal schema is important because it permit to view in a single report different groups of informations joined to a single asset (appointment, tasks, problems, email, etc).

If you really need to use the portal template to condense the information even more, then consider creating a nodb_schema with the following in mind:

• use the custom sql-statement to extend the company asset with the necessary fields for a portal schema.
• for each 'portal-asset' create a similar sql-statement that you combine with the sql 'union' operator (each with it's own particular url-field)
  
• in the view tag use template="portal".

theoretically it should work, I never tried this however.

Cheers

Patrick

 

To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.

[Patrick Pliessnig]

Another way to achieve something similar is to create a nodb_schema with fixed added fields like 'tasks', 'email', etc. then for each added field you add the subtag 'linktext' which adds a link to display the required information below the company asset. I used this. it works fine.

Yet another way is to create single view buttons for each additional set of information and to attach iframed urls (&iframe) to display it below the company asset (or another function). Maybe there is an example of this in the standard schemas.

[Massimo Arnaudo]

Hi Patrick,

it is right, i have added the tag, and the birthdays appeared.

Thanks, now I understand the operation and the importance of that tag.

Massimo

To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupware+unsubscribe@googlegroups.com.
To post to this group, send email to simple-groupware@googlegroups.com.

[Massimo Arnaudo]

Hi Patrick,
the operation is already guaranteed because I can see the tasks, appointments, emails, etc associated with a company taking advantage of the procedure that you have suggested: I have buttons that appear in the view = details that go to the folder of tasks, calendar, email etc applying the filter on the id of the company:

<singlebutton views="details"
name="link_tasks"
displayname="Attivita''"
onclick="locate('index.php?folder=^tasks_@username@&amp;view=display3&amp;filters=company_ext|like|@companyid@');" icon="subitem.gif" />

Now I wish it was more immediate the perception of the whole; an implementation of what already exists.

I looked for the template=portal that you have suggested, but i do not find in the SGS documentation to learn how to use it: do you have documentaion about it to suggest me?

Best regards!

Massimo

[Massimo Arnaudo]

Hello,
I thought of using linkselect tag and insert them in a dedicated view.

This is the new field:
...
<field name="tasks" displayname="Tasks" simple_type="textarea" >
        <onlyin views="details"/>
        <filter views="details" function="linkselect|\n|simple_tasks|id,subject|company_ext=@companyid@|" />
</field>
...

It is quite simple, but I am faced with this problem: the field is left blank, but if I remove the block with the filter (company_ext=@companyid@) appear assets, obviously not related to the company.

I think the filter is right, because of the following tag inserted in the same file (companies.xml) shows the joined task:
...

<singlebutton views="details"
name="link_tasks"
displayname="Attivita''"
onclick="locate('index.php?folder=^tasks_@username@&amp;view=display3&amp;filters=company_ext|like|@companyid@');" icon="subitem.gif" />

...

Any idea?

Thanks

Massimo

[Patrick Pliessnig]

This is probably not what you want, because you add a new field 'tasks' to the database table just for reporting. no good.

The idea is rather to create a nodb_company_summary.xml view with a sql-statement similar to this:

custom_name="(select *, 'TaskSummary' as taskfield, 'EmailSummary' as emailfield from simple_companies) p"

<view name="display" displayname="Display"></view>

<field name="taskfield" displayname="Tasks" simple_type="text"
    <filter function="linkselect|\n|simple_tasks|id,subject|company_ext=@companyid@|" />
</field>

<field name="emailfield" displayname="Emails" simple_type="text"
    <filter function="linkselect|\n..." />
</field>

In the companies schema you then add this view to display the company_tasks schema:

<view name="summary" displayname="riepilego" schema="nodb_company_summary">
</view>

This will do the job. Nothing is written in the database with this method.
But I will also have a look in the method with the portal template.

Patrick

[Patrick Pliessnig]

I am not aware of any documentation about the portal template. understanding the schema file portal.xml should do the job.
I think the solution is a nodb_company_portal.xml similar to this:

<table
   custom_name="(
      select *, 'Tasks' as bookmarkname,
         concat('index.php?folder=^tasks_<user>&view=display3&filters=company_ext|like|', companyid) as url,
         0 as refresh,
         100 as height,
         1 as fullwidth
      from simple_companies
   ) p"

   modulname="CompanyPortal" defaultview="display" orderby"bookmarkname">

   <view name="display" displayname="Portal" template="portal" cols="1"></view>

   <field name="bookmarkname" simple_type="text"></field>
   <field name="url" simple_type="text"></field>
   <field name="refresh" simple_type="int"></field>
   <field name="height" simple_type="int"></field>
   <field name="fullwidth" simple_type="checkbox"></field>  
</table>

As you can see, the sql statement creates a dynamic folder of dynamic urls based on the company table. the url column is a string concatenated with companyid. the whole thing is then merged with the schema definition of the fields and the view that has portal as template.

the sql-statement above ony creates a string for the task-url. to add emails and other stuff you use the union operator like this:

select 'Tasks' as bookmarkname, 'task-url' as url, ...... from simple_companies
union
select 'Emails' as bookmarkname, 'email-url' as url, .... from simple_companies
union ...

to include it, you add in the company schema: <view name="summary" displayname"Portal" schema="nodb_company_portal"></view>

usage: you select a company, then you click on the view tab 'Portal' then the sql-statement is executed and it should (hopefully) display all the stuff related to the selected company.

let's give it a try

Patrick

I looked for the template=portal that you have suggested, but i do not find in the SGS documentation

to learn how to use it: do you have documentaion about it to suggest me?

[MassimoA]

Hello Patrick,
forgive the delay in responding, but only in these days of Christmas I can carve out time with the still fresh mind.
i have now tested your suggestion.

I have slightly edited your code block relative to nodb_company_portal.xml files and now is as follows:

<?xml version="1.0" encoding="utf-8"?>

<table custom_name="(
select
'Tasks' as bookmarkname,
concat('index.php?folder=^tasks_@user@&amp;view=display3&amp;filters=company_ext|like|';companyid) as url,

0 as refresh,
100 as height,
1 as fullwidth
from simple_companies
) p"

modulename="CompanyPortal" default_view="display" orderby="bookmarkname">

<view name="display" displayname="Portal" template="portal" cols="1"></view>

<field name="bookmarkname" simple_type="text"></field>
<field name="url" simple_type="text"></field>
<field name="refresh" simple_type="int"></field>
<field name="height" simple_type="int"></field>
<field name="fullwidth" simple_type="checkbox"></field>

</table>

the problem is that the 'Portal' tab does not display any records, which instead are regularly shown with the search button, and do not display the standard portal layout.
i have tested the select statement in the SGS sql console and it works fine.

Massimo

[Patrick Pliessnig]

Hi Massimo

for a schema to display assets the associated sql statement needs to include certain columns like id, folder, etc, etc. without those columns sgs does not know how to display assets. in your sql statement those columns are not there.

if you start your sql statement like this (as indicated in my earlier post):

select *, .....

you are sure to have a sql statement that satisfies the needs of sgs.
you then simply add what you want like: 'Tasks' as bookmarkname, etc ....

does this help?

cheers
Patrick

off topic: have you seen 'beentouch.com'?
a new voip app made by students in Catania/Sicily for slow internet lines like the ones you can find in southern italy. pretty cool stuff, you create a peer connection with the person you call (no server in between). crystal clear sound. in case you have relatives in southern italy they might be interested.
it's brand new, so still expect technical problems.
sorry for the off topic. I just couldn't stop writing.

[Massimo Arnaudo]

Hi,

after so many months I managed to solve the question that I could see in one place if and which activities are connected to a client.

first of all thanks to Patrick for his patience and useful advice!

Now i have attached the files i have created to do this job, each must be saved in the homonymous folder starting from custom/modules/schema/

The use is very simple, a tab is created and that allows you to check if there are problems, commitments, meetings, notes and emails that are connected to the company and that allows us to view them by following the link.

That do not work very well.

1) I can not get it to work by using the nodb = "true" statements in defining join fields (eg tasks_relations)

2) I can not prevent the display of the @subject@ or @description@ from being truncated after 30 characters

3) in the helpdesks_relations field the construct ^helpdesk_@username@ does not work

I hope it can be useful

Ciao

Massimo

​

​
​

--

[Patrick Pliessnig]

Hi Massimo

Many thanks for sharing your solution. I appreciate that.

I checked your files and have these remarks for potential improvements:

- it seems that you extend your database tables tasks, emails, notes, helpdesk mainly with the db-field company_ext (and some more) and use this field with the relations schemas. then you link these relation schemas using the showselect filter to relation fields in the companies schema.

for the points that do not work, I have some suggestions:

1)
you show the relations field of companies only in the view name="details" in the companies schema. to make the relation fields virtual instead of real db-fields you could customize the sql statement of the details view like this:
<view
name="details" displayname="Dettagli" show_preview="true" default_sql="select *, 'tasks' as tasks_relations, 'notes' as notes_relations, 'appointment' as calendar_relations, 'help' as helpdesks_relations from simple_companies" >
</view>

then in the relation field definitions you could insert nodb="true". this should prevent sgs from creating the field in the db table. then you could delete the relation fields from the db table.

Alternatively (and probably the better solution) could be to use the schema="nodb_subschema" tag to achieve the desired effect instead of the default_sql tag.

2)
in the field definition for tasks_relations you could try with the tag width="a integer value" to make the field larger. if this doesn't help, you need to customize the showselect function which is more difficult.

3)
for each folder you can set a custom identification which is called an anchor in sgs. for example for your personal calendar folder if you go to "edit rights" you will find the anchor "calendar_massimo". this is why a construct like
"index.php?folder=^calendar_@username@" works. If you use the standard helpdesk, there is no anchor as there are no personal helpdesk folders. the solution is to either a) create personal helpdesk folders for each user and add an anchor to each such folder or b) to use the helpdesk field "affecteduser" in the filter.

In the latter case the tag could be something like this:

<link value="@index.php?folder=102708&amp;view=every&amp;filters=company_ext|like|@companyid@||affecteduser|eq|@username@" />

or

<link value="@index.php?view=every&amp;find=simple_helpdesk|company_ext~@companyid@,affecteduser=@username@" />

finally: you use the construct "myclass::companyid" but there is no definition of it in your attachements.

cheers
Patrick

PS:
check this http://www.simple-groupware.de/cms/Manual#DataExport for more about filters.

To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.

[Patrick Pliessnig]

For the helpdesk problem, maybe instead of using the field "affecteduser", it is more adequate to use the field "responsibles" as the case may be.

In this case you would rather use the 'like' and '~' operators ( responsibles contains @username@ ) instead of the 'eq' and '=' operators ( affecteduser is @username@ )