Saving data from a third table for export csv

[albertordz]

Hello I am new to Sugar and I have this problem

I have a built by me module named Backorders in which I included a related field named Account Name.
Also I implemented the CETeams plugin, so one account belongs to one CETeam, saving ceteam_id in Accounts table. I choose the team while creating the account.

When I save my backorder and then go back to ListView, I can export the record(s) to an excel file where it displays the account name and account Id.

I need that when I export the records to csv it displays the CETeam name shown in this relationship:

Backorders Accounts CETeams
account_id -------------> id | ----->id
account_name | name
ceteam_id ---------

I know I can use additional_fields attribute from the realated field in vardefs, but as far as I know it only works with fields from the second table (for example only the records from Accounts table)

The closest solution I found in this forum is to display aditional fields on the DetailView, but I think I need to store the ceteam_name in the backorders table so it can be exported to csv. That leads me to another interrogation, since I need to save a CETeam name, based on what Account the user chose in the accounts popup, Do I need to code Ajax to get that information before save? Or Sugar can manage this procedure in the save method?

Sorry I am still very confused with the framework, I am coming back to php after many years being on Rails.

Hope you can help me.

[snikwahj]

Hi,

You do not need to store the CETeams name in the Backorders table (this is denormalising), you seem to have the storage structure correct with the CETeams data stored in its own table which is related to your Backorders table via Accounts. This is fine. All you need to do is to get the export to include data from the CETeams table in with the Backorders export, right?

The function "create_export_query" found in your Bean file (I'm guessing yours is called Backorders.php) is what creates the SQL query that collects the data for the export functionality. This function simply returns a properly-formed SQL statement that the export uses. All you need to do is to get your data included in this SQL query. You can include whatever data you like in this query providing you can trace a relationship path to it in the database. Just join on the tables you need (your CETeams table which should be joined on id->backorders.ceteam_id) and include the required field(s) - in this case your CETeam.Name field - to the SELECT list.

That should be all that is required. Providing the SQL query returned is correctly formed you should see the field in the CSV file that is exported. The CSV file itself is built directly from this SQL query so you can control the position of the fields and even the field names using the query syntax i.e. "SELECT ceteam.name AS 'CETeam Name'... etc. When specifying field nams like this though bear in mind that the exported CSV file will convert all names to lower case although things like spaces are preserved.

Note that this works for all standard modules (like Contacts, Accounts) too, although editing this function in a baseline module is not upgrade safe.

Hope this helps,

JH.