What you describe is called a conditional value list. Here are some links to get you started:
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Option 1, in the Forum Tutorial might be a simple way to do this if you can craft a calculation based on whether or not a print has been "invoiced". The Challenge to using the method described in all the other links will be to set up a relationship that works for uninvoiced print records, but it's first important to understand the basic concepts to be used to make it work.
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Thanks again PhilMod,
I've gone through all of the resources you provided and more. Ive had some experience with conditional value lists and think ive got the hang of them. But for the life of me I cant figure out a calculation that works, I guess mainly because the calculation has to be unstored to reference the Invoice Details table. Ive tried relating to the table with an auto enter calculation in the invoice details that equals 1 and if the relatinship is valid ( meaning there is a related record with the key being the print sequence id) but cant seem to make it work. Can you provide me with a path that might work for this. Thank you again.
Hmmm, as I look more carefully, not only is this a conditional value list, it's a special kind that is known as a "diminishing" value list. Such a value list automatically drops values from the list as they are used, thus permitting a given value to only be selected once.
You are correct that the calculation is a challenge. And the extra table between Invoice details and prints complicates this.
Please refresh my memory. What is the purpose of the Print_sequence table and why do you select a sequence number instead of the print_id in Invoice Details?
And do you want to limit the choices to only those Prints that have not been invoiced or only those Print Sequence records that have not been invoiced?
Oh yes, and do you have FileMaker 12? ExecuteSQL can be used in FileMaker 12 to simplify some of the details needed to make a diminishing value list work.
So, I'm recreating an access database for a non-computer saavy photographer. He wants it to look and act the same as much as possible.
What he does is, he goes to a layout based on prints and creates a print and defines that prints name and size. He works with limited editions of his prints based on size. So if he creates a print called "Birds" and defines it as an 8x10 print, then he will only sell 50 of that print at that size (hence the need for the print sequence table so that we can track which sequence number he is selling).
The next step is that he goes into an autorize print layout based on the print sequence table. He creates a new record and pulls in a print from the prints table. It uses a Max function based on print id to count how many of that print has been created and creates the new print sequence giving the Birds 8x10 print a sequence number of say 10.
Once a gallery orders a print from him, he creates a work order/invoice for that gallery and attaches the print sequences that are to be sent to that gallery for sale. Also on the invoice, just because a print shows up does not mean that it is sold, its just been shipped at that point. When the print sells he modifies the work order/invoice with the sale price.
So the next time he creates a work order/invoice and selects a line item with a value list based on the value list that I'm trying to create, he wants only to show prints that have not been invoiced yet because the value list would have over 6000 records and he also does not want to make the mistake of adding a print sequence twice to an invoice.
In my research I've come across the lookup function to look into the invoice details table and return a field if it exists or to return 0 if it does not. Then I've created a TO of print sequence with a global field gIsInvoiced with a value of 0 hoping to to return only the records that have 0 in that field. The lookup function acts as a stored calculation and I'm hoping this may be a working solution but have not had time to test it yet.
Some other possible solutions that Ive seen are IsValid and IsEmpty, but have not tested those yet either.
I am using FMPro12 and in my searches have come across the ExecuteSql but wasnt able to find alot of documentation that made me feel comfortable utilizing it as I'm not very familiar with SQL. Thanks PhilModJunk.
and the value list should be limited to print sequences that have not yet been invoiced.
The lack of paragraph format made that one post difficult to read.
Will this value list be a list of print_seq_id values? or Print_id values?
I think you need a value list of all Print_seq_id values that don't appear on any other invoice. I wonder, though, if this will always be the case. What will you do if a print is invoiced, but then returned unsold?
sorry i am actually new to posting in forums as well, it seems I've always been able to find answers to my questions without having to make my own posts.
The value list needs to be a list of print seq values.
I guess if that were to happen he could just remove it from the invoice that it was originally on and then it should show up in the value list again.
There are other ways you can do that, but let's get the basics working.
What you need is a new occurrence of Invoice Details linked to Invoices 1 like this:
Invoices 1::anyfield X All_Invoice_Details::anyfield
(this will work with any fields you select as the match fields as long as you use the X operator instead of =.)
Then this calculation field, cSeqList, defined to evaluate from the context of Invoice_Details 1, will list all print sequence values that have been entered in an invoice:
-1 & ¶ & List ( All_Invoice_Details::print_seq_id )
Select Text as the return type for this field.
For your value list, you can then add another occurrence of Print Sequences and link it like this:
Invoices 1::cSeqList ≠ Unused_Print_Sequences
and use this last relationship for your diminishing value list.
For more on this method and how ExecuteSQL can simplify it, see: Diminishing Value Lists and ExecuteSQL
After posting this, it occurred to me that a scripted alternative may produce a value list that deploys much faster for your user.
You could use the OnCommitRecords script trigger on your invoice layout to run a script that sets a value in a field in the Print Sequence field of every Print Sequence record that matches to one of the invoice data records on the invoice. Then option 1 in the forum tutorial becomes a method that could work as you now can set up an indexed calculation that uses the value in this field to either return the sequence Id or be blank.
For the value list TO you say Invoices 1:cSeqList , did you mean Invoice_Details:cSeqlist (or All_Invoice_details:cSeqlist)?
I tried it this way and at first it did't seem to work until i edit a previous invoice that had a value in the portal row on the invoice layout. From there it seemed to work perfectly. It seems that if there is no value in the portal field than no related records show and you only get a blank value list.
But upon first entering a value then clicking on the portal drop down field it works. Is there an elegnt solution for this behavior. And thank you so much for your insight on filemaker.
That's why there's a negative 1 in this calculation:
-1 & ¶ & List ( All_Invoice_Details::print_seq_id )
also be sure to clear the "do not evaluate if all referenced fields are empty" check box.
The idea is to make sure that there is always a value in this field.
I cleared the "Do not evaluate if referenced fields are empty" check box. Have set the value list to use values from first field of Unused_Print_Sequences:print_seq_id and onlly show related values from All_Invoice_details but the correct value list does not display when initially clicking in the portal field. The layout is based on Invoices 1 and the portal shows records from Invoice_Details 1. If i click the dropdown that field that has never had a value it shows all records even though the relationship seems to be setup correctly. If i manually enter a value in the portal dropdown field then click the dropdown it seems to display the correct related values.