index problem with valuelist

[Jukkie]

Hello,

I work with 3 databases: Orders, invoice & invoice articles.

In the invoice database I have a portal that connects to the invoice articles.

A field called project number has a valuelist which show all the project of the custumer which is selected. (I want only the one that are still open)

Here is what I want!

After selecting a project number it should disapear from the valuelist.

What I did is making a calculation in the order database which make a choice if the project is already billed or stil open. if its open its show the product number and when it is already billed it reports nothing.

So with this field a wanna make a value list, but setting up this value list I get a warning that this field can not be indexed.

So it doesn't work.

What to do?

[jscooper]

Calculations that reference related or global fields cannot be indexed. So, you need to make an indexable "copy" of the exact same information. Here's what's worked for me:

(I'm assuming "project" the the unindexble field you want to index, and ID is a unique identifier for each record)

Create a new calculation field, call it ProjectTrigger, set it equal to if(project,ID,ID).

Create a relationship, call it ProjectTriggerSelfJoin, connect to the same file(invoice articles, I guess) and set it to ProjectTrigger := ID.

Create yet another field called Project_Indexed. Set it to the same type as "Project" and define it to auto-enter a lookup based on the relationship ProjectTriggerSelfJoin, copying the data from the field Project.

Go back and do a relookup on all your old records to update Project_Indexed with the appropriate values.

Now, use Project_Indexed as the source for your value list(s). Anytime the field "Project" changes, the change will affect the value of the field ProjectTrigger, which will generate relookup of the changed value, updating Project_Indexed.

Since you want Project to sometimes contain empty fields, be sure to uncheck the box marked "Don't Copy contents if empty" when defining the lookup.

Jeff

[Jukkie]

Hello Jeff,

I believe your solution does not work for me. I try to be more specific.

In the order database is a field called project number. In the invoice articles database is a field project number. I use the invoice database to make to invoices. In the invoice database is a portal connected to the invoice articles.

This how it works! In the invoice database I have a relatated field invoice articles::project. this field has at this moment a value list of all orders that belongs to the buyer. Also the ones that allready has been billed. I select a project and in the database invoice articles is a relationship called projects this relationship is comparing project number with project number from the project database. Now all the other information is filled in.

Now in the orderdatabase I made a calculation wich say if project number is in the database invoice article show nothing if not show the project number.

Now I need a work arround to get the same information but with no connection to a other database. So it can be indexed.

I hope it's more clear, but it might be i have to tell more about wich relations are.

Jelte

[jscooper]

OK. The same method will work, but do it in the file that has the unindexable field you want to index. It looks like Orders database contains the field you want to index. So...

What you're doing is making a second field that contains an exact copy of the information in the unindexable field. In your case, since Order database contains the field, you should do this in the Order database:

NOTE: In this example, "UnindexablefFeld" is the calculation field you cannot index. ID is the serial number for each record in Orders.

Create a calulation field called IndexedFieldTrigger. Make it = if(UnindexableField,ID,ID)

Create a relationship called Trigger. The Trigger relationship should point to the Order database (this is called a "self-join" -- a relationship that connects a file to itself). The match fields should be IndexedFieldTrigger ::= ID.

Now, create another new field called IndexedField. Make it a lookup. In the Lookup options, set it to use the Trigger relationship. Also set it to copy data from the related field UnindexableField. Uncheck the box marked "Don't Copy contents if empty".

Now, everytime UnindexableField changes it's value, it will automatically cause IndexedField to update itself and they will match. When UnindexableField is empty, IndexedField will be empty. When UnindexableField contains a project number, IndexedField will show the SAME project number.

Because IndexedField is a LOOKUP, it has no real connection to the other database. FileMaker just updates the value of it if the result of the calculation IF(UnindexableField,ID,ID) needs to be recalculated. It doesn't matter that it always returns the same value (ID), FileMaker will try to recalulated it anyway because UnindexableField changed. This will automatically generate a relookup. Thus, IndexedField can be indexed and used in a value list.

Jeff

[Jukkie]

Hello Jeff,

Thanks for responding.

I don't understand de field ID. You say it is the serial number for each record in Orders.

I have to creat the field ID, but I don't understand what you mean by serial number.

What is a serial number and how can I creat such serial number?

I believe it is a language problem at my side!

Greetings Jelte

[jscooper]

aaaaah. OK.

It's a good idea to have what's known as a "unique identifier" for each record: a number that only that record has, so that if you have two records with the same information, you can tell them apart. For example, in the US our tax department (the "IRS") gives everyone a "Social Security Number" or "Tax ID Number" so that they can tell people with the same name apart from one another.

The easiest way to do this in FileMaker is to define a new number field. You can call it "ID" or "Record ID" or anything. Click Options. In the "Auto-Enter" tab, click "Serial Number", the two fields below it should each say "1".

Now you need to populate your old records with ID numbers (since you made them before this field existed):

Put that field onto any layout in the file (just temporarily). Find All Records. Go into that field. Go to the Records menu and select Replace... In the dialog replace the current value with Serial Numbers. Be sure to check the box that says "Update serial number in entry options". Then click Replace.

Now, every record should have a unique number with it. That number can be used in a relationship from that record BACK TO the same record.

As you create new records, this number will automatically be filled in.

I hope this helps. Sorry about the language problem .. the only Dutch I know is "Van Halen"