Portal riddle

[WillJD]

I have been operating a FMP 4 database for several years (updating from

earlier, flat-file versions, etc.) to run a translating business. But I don't know much about designing databases.

My system keeps good track of Jobs (when they come in, go out, how much

to charge, which translator/s did the work, how much each bills me,

etc.) as well as, through relationships, using and accessing client

details, translator info, etc. Each job also displays the corresponding

invoice number once it has been invoiced.

The Invoices file keeps good track of invoices and works wonders

generating reports of one sort or another, but this is where the

problem lies.

The Invoices file uses a line item file where the detail info for each

job is stored and then displayed via portals in Invoices, i.e. one

portal displays info from Jobs where I identify the jobs to be

invoiced, then another portal displays charge details for that job from

Line item and the invoice is printed out using that portal. But it doesn't work when I want to include more than one job on the same invoice. In other words, if I choose Job n

[Kurt Knippel]

quote:

---

Originally posted by WillJD:

Maybe I should dump the portals and revert to the repeating fields

---

Dear gawd man, do NOT use repeating fields! They are a holdover from days gone by.

If I understand you correctly, you want your portal to display information from, as an example Jobs #123 and #456. While right now you can only get it to display one or the other.

Ok, this is a bit of a departure from traditional relational design. Normally when you define a relationship you are defining it between 2 fields, each of which contains a SINGLE JobNumber. The trick is to set the field on the right side of the relationship to contain as many JobNumbers as you want to view or include on the invoice.

Filemaker allows the implementation of what is known as a multi-line key (or Multi-key). Filemaker will related one or more values in one field to one or more values in another fields. It evaluates all values to all values (so the order makes no difference), but the values MUST be on seperate lines, that is seperated by paragraph characters.

[WillJD]

Thanks for such a prompt reply. You have definitely grasped the idea and given me the answer I'm after, but I'm afraid I need it spelled out to me a little clearer. I know how to set up a relationship but I certainly don't know how to make it "multi-key".

Many thanks

WillJD

[Kurt Knippel]

You are thinking about this too much. A multi-key relationship is the same as any realtionship, setup the same way and used the same way.

The only exception is that instead of relating ONE value in a field to a value in a field, you are relating one or more values in a field to one or more values in a field.

Say the results of the normal relationship from gJobNumber_global in the Invoices file to the iJobNumber_key in the Jobs file would be all of the records whose iJobNumber_key was equal to the value in gJobNumber_global.

So that if gJobNumber_global = 345, the portal will display all the information related to Job #345.

Because Filemaker relates all values in a field to all values in a field, if gJobNumber_global = 345

123

876, then the portal will display all the information related to Jobs #345, #123 and #876.

See what happened?

[WillJD]

I probably am thinking about it too much. But having gone away and read and re-read your answer and still drawn a blank, I can only say that I feel more and more of a beginner...

I cannot see how to make the relationship multi-key.

Having set up a relationship based on a global job # key in Invoices and a serial number in Line item, I cannot enter more than one job number in the global, it does not respond to Return, and if I separate the values, it returns no data in the portal.

Scriptology describes several advanced relationship types, including global, concatenated and time-based, but I can find nothing about multi-key relationships or about entering more than one value.

Please spell it out, because no, I don't see what happened. Nothing happened. I feel stupid, that's what happened.

Thanks

WillJD

[Kurt Knippel]

You are definately doing something wrong.

PUT EACH VALUE ON A SEPERATE LINE.

As in:

123

456

789

012

and so on. This will cause Filemaker to relate to all those values. Also make sure that your fields are defined as TEXT and not as numbers. Filemaker does not recognize multiple lines in number fields.

[Kukuberra]

I think I understand, it took me a moment to do so. You mean that in Database 1 field Number 1 you would type in, seperated by a return, all the values you want compared to the field in Database 2.

Will this work if there are multiple entries in both fields?

Kuku

[WillJD]

Hi. Back at my invoicing problems after a prolonged absence, I was delighted to see that, indeed, I was trying to sort this out using a Number Global field. As soon as I changed the format to Text, it worked perfectly, reflecting immediately as many jobs as I cared to type in the numbers for.

But there's a problem. All 283 invoices reflect exactly the same info for different clients on all the different dates!

This has always frightened me off using Global fields, the idea of the value being the same for all records. Because I just don't grasp it or its usefulness, since obviously a file with 283 identical invoices is no good.

Where's the problem now? I'd be very grateful for your continued help and input. Thanks

[Kurt Knippel]

Well if you do not want to see the same information on all Client records then you have two choices. Either changes the global to a normal field, which make it unique to each client record OR change/clear the global field whenever you switch records in the client file.

[WillJD]

OK, so now it works the way I wanted it to. I'm really grateful, many thanks.

But, please, tell me why on earth anyone would want to use a global field in this situation, i.e. under what circumstances could anyone not care about all the records showing the same data. I find this incredible and I'm obviously missing some major point about databases, but please try to get this across to me (though I realise the post should now be in a different section).

Again, many thanks.

WillJD

[Kurt Knippel]

In your specific case, you do not.

However what if I want to see a set of data regardless of what client record I am on?