Layout... Is this impossible?

[JTSmith]

Ok, to give you some background, my database has these tables:

1. Properties

2. Jobs

3. Customers

4. Quotes

I want a Layout called "Dashboard", that basically has 4 columns, each titled the table names. Then I want it to say what "red flags" or "warnings" I have.

For example, some of my warnings would be "A quote that is still open and it's been 7 days and I haven't followed up".

Right now, I don't need to be able to pull/link to the exact record, but I would like it to say in the "Quotes" column: "You have X number of Quotes that haven't been followed up on in 7 days"

Under the "Properties" column, it might say: "You have X number of Properties that do not have a fax number listed".

I have a whole list of "warnings" I would use, and I would prefer that they only show up if it's more than 0 records, but if I have to list them all on the "Dashboard", I can use conditional to make them red...

Any ideas? I'm lost... The problem is, I would easily tell me a piece of information that could prevent an expensive mistake.

Thanks!

[JTSmith]

Ok... looks like I might have 1 option, by using a whole bunch of global summary fields and calcs, and summary fields on the "Dashboard Table"

[mr_vodka]

If you have a "whole list of "warnings" then perhaps using a script to find these scenarios and then populating the count of the results into global fields or variables would probably be better.

[JTSmith]

That might be a little out of my 'field of expertise'...

What I did was create Calculations based on If statements, and they would either be 1 or 0, 1 being that it meets the criteria for a warning. Then I made summary fields for each calculation, showing the total count. Then I did a 3rd field that's a calculation GetSummary, which is a global field showing the number. Then on my table "Dashboard", I have lines that say what the warning is and the number of problems with each warning.

If there is a quicker/easier way, I'm wasting a lot of time making 3 fields for each warning. Plus I end up with a whole bunch of fields...

[Ron Cates]

I would probably do this by creating a relationship to a TO for each warning with the criteria for each relationship based on the warning criteria. Then you could have one calc in each table you're checking set to Get ( FoundCount ).

For example, some of my warnings would be "A quote that is still open and it's been 7 days and I haven't followed up".

For this example, you might set a calc in the Dashboard TO set to unstored and equal to "Open" and connect it to an "OpenQuotes" TO equal to status.

Then an unstored calc date field with Get ( CurrentDate ) + 7 and connect it to your "OpenQuotes" TO using greater than or equal to the quote date.

Then with a calc in the "OpenQuotes" TO set to Get ( FoundCount )

Now you can put OpenQuotes::foundCount on your Dashboard to see the number of open quotes older than 7 days.

Of course for a long list of warnings you end up with a lot of TOs and relationships. So maybe it's just a matter of prefference. Not saying this would be better than the way you've done it, just offering a different approach.

[mr_vodka]

Wait I just noticed that you were on 12. I would use the ExecuteSQL function. This would probably be your cleanest and fastest way to go.