I can put a job number field in the new database and tie it to the job number in the jobs database to connect them
... and change the = symbol to the X symbol.
Wow. You're amazing!
What exactly does the "x" change?
Do you know of a way to limit the records in a portal also? Like if I only want the portal to show records in a certain month, or the 5 newest records...?
> What exactly does the "x" change?
The X ( cartesian operator ) takes ALL the records of the related table, whichever field connects the two DB or tables, but both fields MUST be not empty.
> Do you know of a way to limit the records in a portal also? Like if I only want the portal to show records in a certain month, or the 5 newest records...?
Yes, experiment with a global field ( month ) in the new table ( or DB ) related to the other table with a calculation field, result number.
For a range you may need 2 globals.
Obviously, i'm pretty new to Filemaker and i do not have much experience with global fields.
Could you please explain in a little more detail how to sort the portal into months and how to make it only show the newest records?
I owe you one!
Before we go on with global fields, I need to know if your solution must run on a single user or multi user enviroment.
I guess it would be mutli user. We do have Filemaker server and I believe about 8 people use our Filemaker and have input into the Jobs database. Only a couple people are going to be able to see this new database though.
You can create global fields in your "control panel" database to use as joins to your "jobs" database that will act as filters for the portal.
For example you can create two global date fields in your control panel. Then link the first global date field (we'll call it start date) to a date field in your job table (a due date, award date, delivered, whatever type of date you may have). The link should look like this
control panel::start date =< job::due date
you would do the same for the second date field (we'll call it end date)
control panel::end date => job::due date
Add the start date and end date fields to your layout with a drop down calendar. Then you can enter a date range and have your portal filtered by that specified range (as long as the start date is before the end date). To keep a user from entering an end date that is before a start date you could use conditional formatting to change the date to red ("formula is" control panel::end date < control panel::start date) .
using this same method you could continue to add globals that join to your job table via the same relation ship. You could create a global field "control panel::delivered" and add it to your layout as radio box set of "yes" or "no"
Add the join
control panel::delivered = job::delivered
You could now filter by delivery status as well as date. You can also see both delivered and undelivered if you shift click both options in your pull down.
One thing to keep in mind is the portal will show noting if you don't populate all the link fields. For this I would add to the file an opening script that populates the required fields with the most common search (i.e. undelivered projects for the current week).
You can also do custom sorting of portals. This requires two fields in your jobs database. A global to select the type of sort and a calculation that we'll sort from. You would add the global "jobs::sort order select" to your control panel with a pull down value list of the kinds of sorts you'd like (say due date and delivered).
In your calculation field "jobs::sort calc"
case (jobs::sort order select = "due date" ; jobs::due date ; jobs::sort order select = "delivered" ; jobs::delivered; jobs::due date).
in the join between control panel and jobs set the jobs table to sort by "jobs::sort calc". Also the sort check box for the portal on your control panel layout must be unchecked.
If you've done this right when you change the global field "jobs::sort order select" the portal should resort itself.
Hope this helps