This topic is locked
[SOLVED]

 Export using a custom view

11/30/2018 10:55:32 AM
PHPRunner General questions
G
graham.barlow@tech4t.com author

Hi. We have two tables in a PHPR system - 'companies' and 'contacts', linked by a unique id.
The 'companies' table is our main system table that links to a Bing map to make geographic selections and reflect this on the 'companies' data table.
We also have a custom view (a join of contacts and companies) that lists all 'contacts' belonging to 'each company' in the 'companies' table.
What we need is: when clicking on the export button following a geographic selection made on the 'companies' table, rather than seeing and selecting from the field list from the 'companies' table, instead we want to show the list of fields from the custom view - the mix of 'contacts' and companies'.
Then on clicking export, the output to excel is from the 'list of contacts appended with the company data' from the custom view, (using same unique ids), rather than from the companies table
What is the best way to achieve this please in V9.8 and V10
Thanks

lefty 12/1/2018



Hi. We have two tables in a PHPR system - 'companies' and 'contacts', linked by a unique id.
The 'companies' table is our main system table that links to a Bing map to make geographic selections and reflect this on the 'companies' data table.
We also have a custom view (a join of contacts and companies) that lists all 'contacts' belonging to 'each company' in the 'companies' table.
What we need is: when clicking on the export button following a geographic selection made on the 'companies' table, rather than seeing and selecting from the field list from the 'companies' table, instead we want to show the list of fields from the custom view - the mix of 'contacts' and companies'.
Then on clicking export, the output to excel is from the 'list of contacts appended with the company data' from the custom view, (using same unique ids), rather than from the companies table . Hope this helps. If I understood your question.

What is the best way to achieve this please in V9.8 and V10
Thanks


Update:
Since you have a join statement ( Master / details would work also ) , I would do it the simple way. in the fields section of Contacts . Set Customers field that makes it unique as filter by Contacts in field settings in Contact view . Then in generated output Click a Company left side on list page and click export selected records . This will give you all the contacts / companies that you are looking for to export . Only issue here is you have to export each Company separately . If not design a basic report with view the same way and you will get all at once and then export all to excel . I use 9.8 haven't tried in 10 yet. Hope this helps a little.
Not sure what you meant by geographic selection , That may change the whole thing. Is this a map icon your clicking on? explain further if needed.

G
graham.barlow@tech4t.com author 12/3/2018



Update:
Since you have a join statement ( Master / details would work also ) , I would do it the simple way. in the fields section of Contacts . Set Customers field that makes it unique as filter by Contacts in field settings in Contact view . Then in generated output Click a Company left side on list page and click export selected records . This will give you all the contacts / companies that you are looking for to export . Only issue here is you have to export each Company separately . If not design a basic report with view the same way and you will get all at once and then export all to excel . I use 9.8 haven't tried in 10 yet. Hope this helps a little.
Not sure what you meant by geographic selection , That may change the whole thing. Is this a map icon your clicking on? explain further if needed.


Thanks for reply. I was hoping that we could add some code to events to set the customer table export to pick up fields from custom view rather than customer table - when clicking export
Re geographic selection. We have integrated Bing maps so that the map is set to display in a dashboard with the companies table underneath. We have a button to display any records selected in the companies table on the map. We then have map tools to let the user draw a shape around a selection of records, and on pressing apply button, it filters the list of records displayed in the companies table underneath.
So with those records selected, we then want to export to an excel spreadsheet the contacts and companies for just those companies - all records in one export

admin 12/3/2018

I'm not sure I follow. You need to export data from a different table or view? Point users to that table or view export page then.

HJB 12/3/2018

https://www.xlinesoft.com/livedemo/phprunner/livedemo81/Dashboard_map_dashboard.php
...like in the demo above, your custom view (mix of companies and contacts) could

appear resp. reflect the BING mapped regional search on the left side.
With of course your extra export button in it to go ...

G
graham.barlow@tech4t.com author 12/3/2018



I'm not sure I follow. You need to export data from a different table or view? Point users to that table or view export page then.


What I want is first - keeping the 'companies' table selection and without changing to the 'custom view' - show the list of fields from the 'custom view' (contacts table linked to companies table) when we press the export button on the 'companies' table. By default we get just the fields belonging to the 'companies' table.
Secondly, once the 'contacts' and 'companies' fields are selected for export, rather than export the selected records from the 'companies' table, instead export the selected records from the 'custom view' so we get the contacts exported as well. The 'custom view' uses the same unique id as on the 'companies' table

G
graham.barlow@tech4t.com author 12/3/2018



https://www.xlinesoft.com/livedemo/phprunner/livedemo81/Dashboard_map_dashboard.php
...like in the demo above, your custom view (mix of companies and contacts) could

appear resp. reflect the BING mapped regional search on the left side.
With of course your extra export button in it to go ...


Understand. We already use a dashboard to show Bing map above table, but we want the table to display just one unique record per company that then displays on the map. The issue we have is that there can be multiple contact records for each company and we don't want to show those in main table. We just need ability on export from companies table to also include the contacts records alongside the company records...
Companies table -

ABC Company Address Lat/Long
Export from custom view...

Joe Bloggs ABC Company Address Lat/Long

Fred Stairs ABC Company Address Lat/Long

Janet Long ABC Company Address Lat/Long

HJB 12/3/2018

https://xlinesoft.com/phprunner/docs/_view_as__settings.htm
... the only workaround from my end, though a truly bizarre one, is to make it as follows:
To set different field formats for different pages enable the "Use different settings for all pages" check box. Depending on selected format you will see different box-dialogs.
(View second screenshot within the URL mentioned above, upper left checkbox, labeled "Use different settings for all pages", refers...)
Say, you use your mix of companies and contacts page under LIST page (in junction with BING MAP to filter by regional search on the map) that way, you hide the contact names and else

by choosing the "checkbox" as display format which is just presenting an "empty checkbox" (thus anonymous field) while under "EXPORT PAGE" settings, you define text field format as to

enable to see same by then.
I do not own the skills to provide a code snippet on a list page, originating from another table, via BING mapped filtering, to then "emulate" such filtering process to another table

on export related issues as wished. Maybe admin has another proposal, yet from my point of view, we talk about two different things here to get combined into one button action at all.

admin 12/3/2018

Modify SQL query of the page in question to use JOIN and join the second tables. You can make fields from the joined table only appear on the export page.

G
graham.barlow@tech4t.com author 12/4/2018



Modify SQL query of the page in question to use JOIN and join the second tables. You can make fields from the joined table only appear on the export page.


Thanks Sergey. However, doing that doesn't export multiple contacts for each company record. It just exports the first contact record for each company.
If I change the join, then the main companies table has multiple rows for each company (corresponding to the number of contacts in each company) which we don't want to happen.
Hence my concept of using a separate view and linking that to the export function. But I cant see how to achieve it

admin 12/4/2018

I see what you saying. You sort of need to select master records and export details.
So then you need to follow my original advice: "Point users to that table or view export page then". Let user select master records and via custom button mimic "Export selected" behaviour of details table.
The way you ask this question is very confusing though. Original questions was about selecting fields from details table when in fact it about records in details table. Anyway, this can be done with the help of some custom coding.

G
graham.barlow@tech4t.com author 12/5/2018



I see what you saying. You sort of need to select master records and export details.
So then you need to follow my original advice: "Point users to that table or view export page then". Let user select master records and via custom button mimic "Export selected" behaviour of details table.
The way you ask this question is very confusing though. Original questions was about selecting fields from details table when in fact it about records in details table. Anyway, this can be done with the help of some custom coding.


Thanks Sergey. I understand what you say and also yes, I should have perhaps outlined the issue as needing to be able to also select the 'details' table when exporting the 'master' records.
Could this be a future enhancement for PHP Runner do you think - being able to select 'master' and 'details' table/s also, at time of export - like you can with print?

admin 12/5/2018

The idea of export is that you can import it back. You cannot export master and details together and then import it back, they have different database structure, import program won't be able to tell the difference between master and details records.
You just need to follow my suggestion.