New Project, kind of odd

[macviolinist]

Hi everyone,

I've received great help from here in the past, so I am back to ask more questions.

I am trying to put together a FM solution for the company I work for. We write and process a huge number of SQL queries every day. I can't go into much detail because of an NDA, but what I need to put together is a combination tracking/formatting system.

I am developing this using FMP 8.5 for Mac OS X.

The way we do stuff is by client. And each client will have multiple queries associated with it.

In general, my idea so far is to have two related tables, one for each client, and one for each query. But I'm not sure how to associate the query with the client. So, if I go to search and find a client, I need to see not only the client but also every query that's been run for that client.

Then I need to build an interface that will produce a layout that formats data as an SQL query.

It would be a huge bonus if I could easily output that layout information to a webpage that serves as an html wrapper for our query analyzer (it sets up a queue and stuff).

As you can imagine, this is a complex thing. I need an input layout that gives me the option of adding or removing criteria, I need to be able to tell it that query x is the same as query y, I need to be able to nest one element inside another. I need an output layout that I can just copy/paste (or even better map to a field in a webpage).

Anyway, that's the general idea. So am I right that I only need two tables? If so, how do I connect a given client with it's particular jobs?

I don't want to ask anyone to do this for me, although I am sure many of you could.

But I want to make sure that the solution I end up with is massively scalable. I don't want to have to go and change the basic structure after we've been using it for a while.

Many thanks,

MacV

[mickeyfinn]

I can get you started with the relationship. Two tables, as you suggest, Clients and Queries. Each table will have a text field with an auto-enter serial number (Client ID & Query ID). The Queries table (being the child table to the Client parent) needs a Client ID text field also (to establish which client it "belongs" to. Each query record should have this Client ID field content match the client record serial number it belongs to. In the Relationship section of Define Database, connect the Client ID in the Clients table to the Client ID in the Queries table. This will give you your relationship.

As for layout issues, look up portals in the help files. This will help you view Queries associated with each client inside a client record and can also allow you to create new queries that are automatically related to the Client record you create them in.

[Genx]

Change every occurance of "text field" in the suggestion above to "number field" and you should be right.

The actual formatting of your sql queries may require some fairly complex calcs so give us a yell when you get up to that.

[macviolinist]

Thanks, guys. I appreciate the help.

As always, big projects move slowly. Anyway, this is my status:

I decided to separate the DB into tables.

1. Job_Number

2. Job_Info

3. Query_ID

4. SQL_Code

Each table is related to each other table through the Job_Number field.

Most queries will include the same bits of code, so I am storing the reused code in a separate table and recall the code via a portal and have that included in a template that should be generated when a new query is written for a job.

But sometimes the query alters slightly. Is there a way of telling FileMaker to select a different bit of code in this other table based on, say, a checkbox?

As for formatting the actual code, I can do it with custom text calculations, yes? To do this, I think I need to have input boxes that can be defined as variables that I can place in predefined code--code called from the code table. How do I define a field as a variable for a calculation? Specifically, I need to wrap input numbers in parens.

Thanks again for all the help. I never presented myself as a DB expert, but I got stuck with developing a new solution for our aging system. You know how big businesses roll. . . "Hey, let the new guy take care of it!"

Am I on the right track as far as placing bits of code in a different table?