Database to DataGrid

[cbarnhart]

I have two datagrids that are being populated from a mySQL database. One grid shows organizations and the other datagrid show news articles about each organizations. So the idea is a user can click on an organization (datagrid 1) the second datagrid (datagrid 2) will show all the articles about that organization. I am using a common field (OrganizationID) to link the two tables.

My question is this: not all organization have news articles, so it would be great if I could change the type color for organizations that have articles so the user could scroll through the organization grid and have a visual clue about which one have article (example organization in red have articles). I am at a loss where to even begin on this.

Any suggestions?

[KennyR]

I have a suggestion but I have little knowledge about this compared to others.... What I would do is set up a query before populating the grids. So since you have a relational database through MySQL , query the names of the companies that have entry's in the news table then write a handler that says something like set the foregroundColor of fld "whatever" to "red". I know this is vague but I use query a in my relational databases to set properties of my controls before data is populated in my data grids . I have to work in the morning but will be doing some coding , so ill post some ideas and script if no one has helped you....hope this gets the wheels turning for ya!

Kenny

[dave_probertGA6e24]

Hi,

Add the count() of articles to the results from the SQL for the first datagrid, and then modify the behaviour script (of the form, or of the column) to change the background colour based on the count value. This assumes you understand SQL Aliases (AS) - if not then please do a little search on google - they are not hard to understand.

eg. SELECT a.*, count(b.id) AS the_article_count FROM orgs AS a, articles AS b WHERE ....

In the behaviour script you would then have access to a variable like pDataArray["the_article_count"]. Please read the livecode lessons on data grids to find out more about changing the behaviour scripts in the correct places. Once you get the hang of modifying Datagrids with extra code then you open up a wide range of extra abilities for the data display.

Hope that helps,
Dave

[bangkok]

so it would be great if I could change the type color for organizations that have articles so the user could scroll through the organization grid and have a visual clue about which one have article (example organization in red have articles). I am at a loss where to even begin on this.

If I understand correctly, your question is not related to DB, but to datagrid.

You'll find :
http://forums.runrev.com/viewtopic.php?f=7&t=7865

a stack that shows how to apply a background color to a line in a datagrid, linked to a value in a cell (in the same datagrid).

[cbarnhart]

Thank you for pointing me in the right direction. I did go the route of creating a "count and alias" and created a view. I am accessing the sql view to collect my data and populate my datagrid.

[cbarnhart]

New Question....I altered the Column Behavior to change my background color when the "Number_Of_News" is greater than "0". Works great on the initial load but then when you scroll down in the dataGrid the background color shows up on lines when the value is "0". A refresh grid fixes the problem but only until you scroll again. Any Ideas?