Jump to content

Portal riddle


This topic is 7507 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites


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.

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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


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

See what happened?

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

You are definately doing something wrong.


As in:





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.

Link to comment
Share on other sites

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?


Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

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 smile.gif" border="0

Link to comment
Share on other sites

This topic is 7507 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.