Displaying information from other databases

[Danielg42]

Hello everyone.
I've run into another issue with my custmization of Sugar.
I need to have a custom field in the accounts module display the name of the sales person in charge of the account.
The sales people are in charge of areas based on zip code. So basically I want to run a php script that checks the zip code of the currently opened account, then checks our sales person database for the name associated with that zip code. Unfortunately, my attempts to use a php script in a custom field have failed, so I need some other ideas.
Is there any easy way to make this work?

[kbrill]

can you post what you have tried so far? This would be a great use of a 'after_retrieve' logic hook script.

[andydreisch]

Ideally the Studio s/b able to pull related field(s) based on current value, of Zip in this case.

But ... I'll make sure to create a Wiki article basede on Danielg42's input.

Andy

[Danielg42]

Well I haven't been able to get anything working properly in sugar and my knowledge of php is minimal.

So don't laugh too hard.
Here's the php required to display the zipcode and sales person in a table.

<?php

mysql_connect("localhost", "root", "test") or die(mysql_error());
mysql_select_db("sales") or die(mysql_error());

$zipcode=92605;  //put sugarcrm's variable for the zipcode here

$result = mysql_query("SELECT * FROM salespersons where zip=$zipcode") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Zip</th> <th>Name</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['zip'];
	echo "</td><td>"; 
	echo $row['sls_name'];
	echo "</td></tr>"; 
} 

echo "</table>";
mysql_close($con);
?>

What I attempted was sticking that into an html custom field. Apparently the html field only displays html though.

If you could explain more about the logic hook you were talking about, I'd be very grateful.

@Andy
What do you mean by the studio's s/b? Am I missing something obvious?

[kbrill]

Well I haven't been able to get anything working properly in sugar and my knowledge of php is minimal.

So don't laugh too hard.

What I attempted was sticking that into an html custom field. Apparently the html field only displays html though.

If you could explain more about the logic hook you were talking about, I'd be very grateful.

I see what you are trying to attempt. This is not a selection, just a list right?

[Danielg42]

Hmm...
Yea, I guess it would be a list.

[andydreisch]

... Andy, What do you mean by the studio's s/b? Am I missing something obvious?

Sorry. I think a nice addition to Studio screen layout editor (Admin -> Studio) would be the ability to identify and display related information based on the contents of a field value in the current record. In your case, defining a lookup of a rep's name based on the value of the ZIP field.

Andy

[leenwebb]

I've done something pretty similar in my own sugar -- here's what I did (for DetailView):

1) In custom/working/modules/****/DetailView.html, find the place you want the list to be. Then put in an xtpl variable as a placeholder, like "{ZIP_LIST}".

2) Go to Studio and "Save and Publish" that layout ( Accounts -> DetailView, for example). That will take the custom/working file with its new variable and copy it to all the right places.

3) In modules/****/DetailView.php , put your PHP in and declare a variable that contains all your HTML, let's say "$zipList". Then put in an xtpl line that will shove that variable over into the HTML. Like this:

$xtpl->assign("ZIP_LIST", $zipList);

My own sugar is full of things like this, and they've been working great for me. You can go way more complicated if you need to, using outside PHP queries to populate dropdowns and other things. Good luck!

Eileen

[Danielg42]

Thanks a lot everyone.
Kbrill has offered to help me out by writing a module for me.
However, all of this information has been extremely helpful by increasing my knowledge of how sugar works.

[Danielg42]

I've got it working through your method leenwebb.
Thanks.

The only issue is it seems to have broken the studio. In order to use the studio, I have to remove my php, use it, then put my php back in. Besides that, there are no real problems.