Jobs - Line items

[Dianne]

I checked on my line items layout a couple of weeks ago and found that some of the information is missing, namely the details that are supposed to be looked up from the job sheet (name, company which job relates to)

The Name and company info is there for some of the line items at the beginning so I figure I have done something which has upset the connection. I have been testing unsucessfully for a couple of weeks and I can't get this info to show up now.

I have a one to many relationship from jobs to line items and also a one to many relationship from products to line items (products are showing up fine)

Is there anything I can check or anything I am missing...

Any advice/suggestions would be much appreciated

[SlimJim]

We may need more details. I am guessing that your relationships are via ID numbers so Job_ID -< Line_Item_Job_ID.

The first thing to check is whether the Line_Item_Job_ID numbers are in place in the LineItem records.

[Dianne]

I have job numbers fields in both Job sheet and line items and the Job Sheet job_number = Line Items job_number with creation and deleation of records allowed in Line items - its really odd. I have just had another play around with it and it worked with one job (brought up the name of the client and company) but not with the next one I did?...

[Dianne]

and I have just tried it again with the same contact id and now it doesn't work again and I haven't changed anything!

[SlimJim]

Do you have more than one occurrence of the Line-Items table in your relationship graph? Could some of the fields be pointing to the wrong TO? You mentioned a contact ID in the last post. Where does that come into this picture? Sorry to keep asking questions but I am trying to understand what structure you have.

[Dianne]

Hi Slim

thanks for your interest in this - questions are fantastic...

only on occurance of LineItems in my relationship graph.

This is not a multiuser solution - only I have access to it

Process is:

Fill in Job Sheet fields detailing client, company job number etc...a Portal shows related records from Lineitems within job sheet.

The fields in the portal are from the lineitems layout so I am filling in some of the lineitems data remotely from the Job sheet layout.

So I am expecting the Client name, Company info to be carried over into Lineitems as I am creating the record in Job Sheet.

In Job Sheet I have a Contact ID field with a drop down list which stores all my clients and when I select from there, the name, company info is filled in automatically.

The thing that has really got me is that it works sporadically - so some of the lineitem records have the client info and some don't.

Let me know what you think

Thanks a million, Diane

[SlimJim]

Hello Diane

OK. Lets look at the minimum see if I have it correct. A job Contains a Job Number and a Client Number the client number relates Many(Job Table) - One (Client Table) and the Job number relates One (Job Table) - Many (Job Items Table) [We'll ignore products as that seems to be working OK and I don't know how they fit in] On a job item line you need only the Job number and whatever product detials etc you enter, as long as the portal is set up correctly the job number will be entered automatically. From that job number you can look back to the client name and number so if you need the client details on a job item layout then you need the fields Client::name, and Client::etc... from the Clients table. As this relationship via the job table is many - one then you do not need a portal here to see these details.

There is no need to store the Client details with the Job line items as the job number gives you access.

[Dianne]

thanks so so much - that works and has filled in all the missing info on the past lineitem records...I used look up after following an example on here, but didn't test it on the example. Could I have the client/company details on line items come from either the job table or the client table?

Also, thrown a bit by your last comment 'There is no need to store the Client details with the Job line items as the job number gives you access'

How does the job number give access? Without the client/company info on the line items table do you mean that I could just use the job number as a reference in job sheet to find the job and therefore find the client/company?

I wanted to avoid not knowing at a glance which client/company related to each lineitem record.

THANKS FOR ALL YOUR HELP.

[SlimJim]

Yes in answer to ".. I could just use the job number..". You should always try not to store details more than once for various reasons but in particular because if you ever have to modify the details you only have to modify in one place. So the Client details are stored in the client table and nowhere else. In the other tables you store only an ID number which directs you to the correct client record, either directly as from the job table or indirectly (through the job number) from the line items table. So in the line item table you have a job number, that job number leads you to the associated client number and from this you can pull out the client details, both into the job layout and into the line-item layout if you want.

{edit} I have read your posts again and I think maybe by using the phrase "pull out" I am leading you to think of lookup. In your Job table the only field you need to collect the Client's details is the ID number. In the jod-item table you only need the job number. On the job layout you drop copies of the appropriate fields from the related table Client (not a portal as there is only one client involved, just put a copy of the Client field on your layout). If you want to see these field on the line-items layout do the same thing - you will find Client in the related table list when you specify the field.

[Dianne]

So the client details are stored in the client table and any other occurance of them in any other layouts in your file should come directly from the client table using specified fields -- right? If I then have to update someones name for example it will automactically update this on all records where that field is specified?

At the moment the client name and company on job table are using look up (from clients) - do you think it would be better if these too were specified fields from Clients instead?

What is the difference between Look up and Specified fields, - is there a general rule for when you should adopt each?

Thanks so much for all your help - I really couldn't have moved forward until I had sorted out the Lineitem issue.

I have the Using Filemaker 7 book beside me now so am going to be on my terrace with my head in that all weekend. Its going to be a hot weekend, must be nice to be by the sea, although I suspect most of London is about to descend on you!

Thanks again for all your help.

[SlimJim]

Yes to the question at the end of the first paragraph. The main reason for using lookup fields is if you want that field value to stay the same - in other words NOT updated. For example, suppose you like to keep on record which contact at the company ordered a job then you would lookup their name and store it with the job. If later you get a new contact at the company then that one stays the same, the new one is available via the client tables but the old one is stored with the job.

Yes unfortunately I am heading for Ardingly on Sunday and expect to arrive there at about the same time as the cyclists - so I think I will spend a lot of time sitting in traffic.

[Dianne]

oh - well I really do need for the name in lineitems to remain the same, if ever I need to delete or change a contact then I would still want the info in lineitem to remain the same as when the lineitem record was created, so I think I do need Look up here - only problem is that is what I had it set to before and it wasn't working!

[SlimJim]

Lookup is an Auto-entry option (it can be scripted if you want) so if you are going to store this in the jobline items table you will need to lookup starting from the job-line-items and lookup from the related table clients. Then choose the field you want from the list. Do this for each field that you want to store. Lookup happens when the record is created so if you are creating job items via a portal and have the lookup field in the portal then you should see it drop into place when you commit the record. If you already have records with incorrect or no values then you can force a re-lookup. To do this you have to "select the match field" which will be the job number and then do a Records > Relookup.... Be warned this will update all the records in the current found set with the current data and in view of the reason for using a lookup make sure that you have the correct found set.