filtering portals

[veganboyjosh]

i've looked and from what i've found, i know that i need a global field somehow in order to do what i need/want to do, and i _almost_ understand why/how it works that way, and then my mind goes blank.

i have two db's:

a client file:

obvious info about clients, each client has their own serial number.

a job file

each job= one record, with date of job, which client it was for, which subcontracter did it, etc. each job has it's own serial number

a billing file

each client gets one record per month, with all jobs for that month listed in a portal along with the specific job info(date, sub, priceetc)

each record/invoice has it's own serial number.

so far, i've been able to set up the portal in the billing files, so that for each client, it lists all the jobs done for them.

now i need to filter the portal so that it's only the current month's jobs that appear in the billing record.

i thought i had the solution to this problem when i figured out the portal, and then i find out i need a global field. nice.

i looked at the examples at www.databasepros.com as someone previously suggested, but they kind of skipped over the basics of what i want to do in order to make it flashier/easier to use, and i just got confused.

any help is greatly appreciated, and thanks in advance.

josh

[PTKen]

I think all you will need to do now is a search for the month you wish to display. In your billing file, you also need a relation to the job file that relates to the Job file's date of job field. Just make sure you calculate the month/year to search for and find all records that match both month and year, ignoring the actual day.

[veganboyjosh]

i tried doing a serach for only the record i want in the portal pior to importing them. the problem i'm having is that when i go to import, imports all the records for any client that has any record in the month that i import.

for example:

customer 1 has 3 jobs that took place in november, and 2 in december, and customer 2 has 5 jobs in september and 1 in december. in the job file, i am able to find and limit the jobs to the month i want. but when i go to import the records, in the portal, all of customer 1's jobs, november and december show up, as do all of customer 2's for september and december.

i know that i need a global field to do this. i'm still a little foggy as to how global fields work. i know that they are constant in every record. i think if someone explained the why and how of the workings of a global field, i would be able to figure it out.

[rshboston]

Try this...

In the Billing file, create a calc firld. For this example, I'll call it Job Link. The calculation should be:

Client Number & Month(Status(CurrentDate)) &"-" & Year(Status(CurrentDate))

This result will concatenate the client number, current month and current year (e.g.: CLNT12-2002)

Now, create a calc field in the Jobs file. For this example, I'll call it JobLink. Make the calculation exactly the same as the calc field in the Billing file, but use the corresponding fields in the Jobs file.

Now, create a relationship using the two Job Link fields. When creating the portal, be sure to specify fields from the Job Link relationship. The result should be that the portal for each client shows only thos jobs for the current month. NOTE: The calculation as written allows only jobs from the current month and year. This way, you don't get jobs from the same month, but different years in the portal.

I'm sure there are more elegant solutions, but this will at least get you going.

[rshboston]

Error in my last post.

The calculation in the Job Link field of the Jobs file should not be the same as the Job Link calc field in the Billing file. Instead, it should use whatever date field you've specified to identify the month in which the job(s) in question fall. My guess is you have a job creation date field for each record. Let's call this field Date Created_Job. In this case, the calculation for the Jobs file Job Link field would be:

Client Number & Month (Creation Date_Job) & "-" & Year (Creation Date_Job)

That should do it - assuming you follow the rest of my previous post.

Sorry for any confusion my original post may have caused.