SELECT WITH WHERE CLAUSE

[mw333]

After using several relationial database systems I am trying to implement a similar structure in FMP.

The current problem is the retrieval of data using a WHERE clause. I have a two tables linked with a one-to-many foreign key. I would like to populate the master record query form with data from the linked table. This I can do using a portal. I would then like to restrict the rows retrieved. In other systems I would use either a VIEW or a simple;

SELECT * FROM TABLE WHERE CONSTRAINT = VALUE

How is this implemented in FMP as I cannot currently find a way without having to write a large number of scripts.

[Kurt Knippel]

quote:

---

Originally posted by mw333:

After using several relationial database systems I am trying to implement a similar structure in FMP.

The current problem is the retrieval of data using a WHERE clause. I have a two tables linked with a one-to-many foreign key. I would like to populate the master record query form with data from the linked table. This I can do using a portal. I would then like to restrict the rows retrieved. In other systems I would use either a VIEW or a simple;

SELECT * FROM TABLE WHERE CONSTRAINT = VALUE

How is this implemented in FMP as I cannot currently find a way without having to write a large number of scripts.

---

Since FMP does not implement the SQL query language, this is not directly possible. However you can perform this kind of thing with a "Find" procedure, when looking into the same file, and with the "Go To Related Record" script step when looking into a related file.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[mw333]

quote:

---

Originally posted by captkurt:

Since FMP does not implement the SQL query language, this is not directly possible. However you can perform this kind of thing with a "Find" procedure, when looking into the same file, and with the "Go To Related Record" script step when looking into a related file.

---

When I do any find I get a full view of all related records that are normally displayed in the portal. Is there a way of making the current LAYOUT selective in terms of portal rather than opening (potentially many) invidual records that match, which works as soon as someone switches to that layout, rather than needing activation by script?

thanks for assistance,

Matt

[Kurt Knippel]

quote:

---

Originally posted by mw333:

When I do any find I get a full view of all related records that are normally displayed in the portal. Is there a way of making the current LAYOUT selective in terms of portal rather than opening (potentially many) invidual records that match, which works as soon as someone switches to that layout, rather than needing activation by script?

---

I am not sure what you are trying to accomplish, but everything is run by scripting. You would structure your scripts to complete the functionality that you want to happen.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[mw333]

Hi there,

Just an example of what I'm trying to do, it's a fairly simple contacts database, where the data is basically composed of data relating to the organisations we deal with, and another database dealing with the people who are at a particular organisation. Clearly people do leave organisations and so we have active people and inactive people. In the main user layout, we just want one 'page' per organisation, and the portal contains contact information on each user who would be relevant. If there were problems they could switch to another layout which has all the people that have ever been at that organisation. Therefore we want to be able to display people selectively based on a status field.

So for my layout it is just a collection of fields that display organisation related data and then a portal to the contacts db.

Hope this makes things a bit clearer.

Thanks again,

Matt.

[Kurt Knippel]

quote:

---

Originally posted by mw333:

Hi there,

Just an example of what I'm trying to do, it's a fairly simple contacts database, where the data is basically composed of data relating to the organisations we deal with, and another database dealing with the people who are at a particular organisation. Clearly people do leave organisations and so we have active people and inactive people. In the main user layout, we just want one 'page' per organisation, and the portal contains contact information on each user who would be relevant. If there were problems they could switch to another layout which has all the people that have ever been at that organisation. Therefore we want to be able to display people selectively based on a status field.

So for my layout it is just a collection of fields that display organisation related data and then a portal to the contacts db.

---

You would use two seperate layouts with 2 different portals based upon two different relationships.

One the active sceen you will related from the OrganizationID to a cActiveOrgID in the contacts file. cActiveOrgID = If ( Status = Active, OrganizationID, "" ). This way only those contacts with the status set to active will be displayed.

On the all screen simply related from the OrganizationID to the OrganizationID in the contacts file. This will display all contacts ever associated with the organization.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=