Value list based on calculation field

[Doug_Miller]

I have a calculation field (If ( COMPLETED ? 1; "Yes";"No" )) that doesn't seem to give me any results in my value list. If I make the field a text field I am able to get the results necessary. My question is, is it possible to use a calulation field to obtain populated value lists and if so how might I do it?

 

Thank you

[aaa]

Hi, Doug!

Yes you can. In your case your calculation must return "Text". Choose it in return options when you define this calculation.

[Doug_Miller]

aaa,

Thanks for the response. I do have it set to text, but for some reason when the field is set to a calculation field no entries show in the value box. I have a clients db with many fields two of which are pieces completed that has the above formula. Another filed is a global named yes with the value yes assigned to it. I have a copy of the clients db and named it Clients with completed and have linked the two by completed = yes. When the pieces completed field is text all of the values show up in the value list. But when I change the completed pieces to a calculated field there are no values in the value list. I just can't seem to figure out how to get the list to populate correctly with the calculated field which is a must.

[LaRetta]

Two possible issues:

 

1) The global YES must be in the main table with the calculation in the related table. The relationship will not work otherwise because related data can't be pulled through a relationship with a global on the right side. But more likely ...

 

2) You have the calculation set to Unstored. Value Lists must be indexed to work.

 

smiley-smile

[Doug_Miller]

Thank you LaRetta. I knew that the solution had something to do with the index since the field showed index missing when it was a pop up. A little confusing when nothing shows up when the field is a drop down. I guess the value list worked when I changed it to a text field because there something was to index and there wasn't anything the other way. The relationship does work with the global on the right as long as the completed pieces resolves to text that can be indexed. Any idea how I might get completed pieces to update properly without being a calculation field and not having to manually search and replace on the same formula as when the field was a calculation field?

Thank you.

[Doug_Miller]

I should have mentioned that the Unstored state can not be changed because it is dependent on several other fields and FileMaker will not allow it to be stored.

[LaRetta]

Hi Doug, I'm afraid I simply can't envision what you have! However, this is one place that auto-enter (Replace) can shine ... if you use an Auto-Enter calculation on a standard field which references a global (which resides in the same table), that standard field will change every time the global changes (if you change the current record). It can then be indexed because the FIELD can be indexed even though its result is calculated based upon a global. Clear as mud? But I have no idea why you're using a global here at all!

 

If you need more specifics, I would need more specifics. smiley-laughing

 

LaRetta

[Doug_Miller]

LaRetta, maybe I should explain a little further the details of my dilema.

I am a picture framer and have successfully converted all of my databases from dBASE to Filemaker. Everything in my solution works quite well and I find myself continually refining the code for speed, ease of work, the ability to show myself my information in more relevant ways (makeing more money) and yes, for the enjoyment I get from it. My solution has 26 tables most of which are secondary to the more important primary table (eg. telephone numbers linked to clients so that I can have as many telephone numbers I want without the need for a fixed amount of fields).

 

My clients table is linked to a tickets table via a client id. In the clients table I have several fields that total several fields in the tickets table such as current jobs in house, the number of previously completed jobs, the total of completed jobs waiting for pick up, monetary balances and the like for each client.

 

A customer may come in with 5 pieces to be framed and ask that as soon as any of them are done to call them so that they can come and pick it up. As soon as I complete a job and mark it as so with a complete date in the tickets table it also reflects in the clients table. (calculation field) I have a layout that filters on this information (completed jobs) that I access daily to call people to tell them that their work is ready. This layout pulls telephone numbers from the clients table job information from the tickets table. There is also payment information but at this point it is unimportant unless the customer asks for a balance at the time of my call.

 

When a customer picks up their work the fields in the client table will again change reflecting the new totals since I have marked pickup dates, collected money and such. Because of the nature of my work there may be times when some pieces are complete and the customer needs to know and some times they may not need to know until all are complete. Basically the totals that are important continually change and are dependent on some of the other tables and the progress of the jobs in those tables thus the calculation fields.

 

So now to where my question comes in. I have a layout for when a customer comes in to pick up a completed piece. I have a value list that displays the client names. Obviously not all clients have completed work so I don't want their name in the list. This is where my self join table clients with completed comes in. As stated previously when the join field is text (not a calculation field as text) the relation shows every client with completed work as expected in the value list but doesn't when the field is a calculation field. To keep totals current is my best or only option to change the field to one of text and run the replace formula (If ( COMPLETED >= 1; "Yes";"No" )) after every completion or pick up of a job?

[aaa]

Hi, Doug!

Your field COMPLETED probably can not be indexed, it is reason why your relation dont works. You must find way become it indexing.