solutions wanted Displaying two numbers (Customer and Job number)

[jdenver]

I have created two tables one for customers and another for job information. Each customer has a unique number (say 1234) created when a new customer is entered and each job information has a unique number (say 2431). The tables are linked and work well. I would like to create a field that shows the customer number and the current job number together e.g. 12342431. At present the field I created just shows the customers number and the first job number for that customer and not the current job number. Or is it possible to click on a job in the list of jobs for that customer and have the job number change as this is done. Thanks in anticipation

[mr_vodka]

How do you mark which job is the current one?

[bcooney]

Can you assume that the current job is the last one entered? Then you could create a calc field in Customers

CurrentJobID = Last (customers:jobs_id)

then your concatenated client/job # calc is:

Client_ID & CurrentJobID

[jdenver]

Thanks guys, I've tried two ways and both produce different results. First way was to create a field in the customers table called 'customer_no' set it as a calculation and enter the line: Let ( CurrentJobID = Last ( tb_jobinfo::pk_job); pk_customers & CurrentJobID) then place the field 'customer_no on the form. The second way was to create a field in the Job table called 'currentjobID' and set it as a calculation and enter the line: Last ( pk_job ) and display the field 'currentjobID on the form. When I display a record consisting of a customer and a portal of jobs for that customer both calculations above show two different job numbers. Is there a way to look at the date field to find the latest and display the relevant job number?

[bcooney]

Sorry I didn't see your reply sooner. Have you solved this yet?

It is important to define what makes a job the "current job." Is it the one with the highest jobID?, the highest creation date? What?

The calc field that uses the Last() function and that "finds" the current job must be in customers as I described above. The Last() function will display the last job entered for each customer (highest id) unless the relationship between job and customer is sorted by another field. Do you have a date field in jobs that you can use to sort the relationship customer::jobs?

[jdenver]

That might be the problem, the jobs are sorted by date field so is there a way to get the job number by the latest date in the jobs table?

[bcooney]

I've been on vacation, so I didn't get back to this sooner.

You need to sort your relationship between customer and job by the date field that you mention. Do you understand what I mean by sort the relationship as opposed to simply sorting the job table by the date field?

Then just use the calc field that I mentioned above to get your current job id.

[jdenver]

Ive managed to work out what you ment and have sorted the date field in the relationship window. Everything works correctly now. These just one thing, - why does it work now, I would never had thought to do that, I dont understand why the sort by the date field in the portal didnt work correctly

[bcooney]

The Last() function will get the last child record in a relationship. By sorting by the child's date field ascending, you can be certain that the last related record is the one with the most recent date.

Sorting the portal is a display only thing. It doesn't hep the Last() function at all.

[LaRetta]

You can save a bit of resource here (eliminating the need to calculate or needing a calculation at all). Since you plan to sort the relationship anyway, sort it descending on the date. In this way, you can reference the date in the child table directly. To test that it is working, drop the child date or JobID field directly on your parent layout.

This works because a parent record will always only *see* the first related record. So we can put the 'last' as the first (by sorting the relationship) and eliminate 'finding the last' at all.

If all you wish is display, just place a merge field on your parent layout which is <> [color:green]after you select (checkbox) sorting your child table RELATIONSHIP descending on job date. You don't have to sort it all the time - just set it and forget it!

LaRetta

Edited March 3, 2007 by Guest
Clarified green

[bcooney]

Thanks, LaRetta, much more straightforward approach. At least the sort relationship part was correct! I've always avoided merge fields, fearing that they don't update if the field name is changed. But they do, and I should just get over it, huh.

[LaRetta]

Merge fields are particularly helpful for placing any field which shouldn't be edited by Users (you then don't have to change field behavior to disallow entry in Browse).

And, just like standard fields, it allows alternate formatting (date, number, text, time). For instance, many people create a calculation out of a date field simply to display March 2, 2007 on their layouts when it could be handled as merge or standard field (and formatted differently). Or people create calculations so they can colorize and style different pieces of the merge line or add words between various fields. It is usually unnecessary.

Merge field also allows mixing standard text and the field, ie typing [color:green]Order called in by <> on <> and will shift properly within the merge box (centering and so forth). They aren't a fix-all but they come in handy many times. :wink2: