12 Replies Latest reply on Jun 13, 2012 10:09 AM by letra

    Need Value List from Calc Field

    letra

      Title

      Need Value List from Calc Field

      Post

      Hello Y'all,

      I have a calc field (Carga), and I need to create a drop down value list to be used in a different field...and I need this value list to contain the values from my calc field. FM yells at me and tells me that I can't create a value list from a calc field. So, as an attempted workaround, I created a second field (CargaCopy) that is a text field with a calculated auto-enter result, and copied the calc from Carga into this CargaCopy. 

      CargaCopy is not indexed, but it does not update if Carga updates. So, my value list does not stay accurate. Help.

        • 1. Re: Need Value List from Calc Field
          philmodjunk

          FM yells at me and tells me that I can't create a value list from a calc field.

          You'll need to explain that in more detail. You CAN create a value list that lists its values from a calculation field.

          You can't format a calculation field as value list as you aren't allowed to enter data into a calculation field, but I don't see why you might try setting it up that way in the first place.

          • 2. Re: Need Value List from Calc Field
            letra

            When I try to select Carga as the source fo my drop down list, FM tells me that it won't work because the field cannot be indexed. When I try to change the storage option for Carga to indexed, FM tells me that I can't because it references a related field that's unstored blah blah blah...

            • 3. Re: Need Value List from Calc Field
              philmodjunk

              Yep. The issue is not that it's a calculation field. The issue is that it's a field that cannot be indexed due to it being an unstored calculation field. Stored calculation fields work just fine, but as you've discovered, this specific calculation cannot be stored.

              There are several possible work arounds here. Please describe how you need your value list and calculation to work so that I can recommend one to you.

              • 4. Re: Need Value List from Calc Field
                letra

                OK...

                The calc field is a bit complicated to explain, but being that you're the very one who taught me how to do it, your posts should refresh your memory.

                So, I just need the value list to reflect the values of this calc field.

                • 5. Re: Need Value List from Calc Field
                  philmodjunk

                  That's a start.

                  you have an unstored calculation field defined to return one of these values: "First" ; "Second" ; "Third" ; "Fourth" ; "Fifth" ; "Sixth"

                  But how do you want your value list to work?

                  Do you want all the values "First" ; "Second" ; "Third" ; "Fourth" ; "Fifth" ; "Sixth" to appear in the value list or something else?

                  • 6. Re: Need Value List from Calc Field
                    letra

                    Well, since you asked, ideally all of the values already contained in the Carga field would show up in the list unless the current date is is greater than the date contained in a certain date field...

                    • 7. Re: Need Value List from Calc Field
                      philmodjunk

                      And you will use this for data entry? (sounds more like a reporting type task and that can be done via other methods...)

                      It might be possible to set up a filtered portal where you click a portal row to select that value and enter it into a field...

                      And there are scripted methods you might use with a script, possibly via a script trigger that might update an indexed text field with a return separated list of values to serve as the list of values for your value list.

                      ExecuteSQL is also an option for FileMaker 12 users that can either populate a text field with clickable values (much like the portal idea) or used in a script to update said text field.

                      Is this still the relationship that you are using?

                      YourTable::cYear = yourTable 2::cYear AND
                      YourTable::Date > YourTable 2::Date

                      Is "Date" used here in the relationship your "certain date field"?

                      • 8. Re: Need Value List from Calc Field
                        letra

                        I'm not sure what the difference is between data entry vs. reporting type task, but here's the low down. This db is used to track the import of products. The Carga field contains the names that we use to reference shipments of products. So, the fields we've been discussing are contained in records on the Shipments table in my db, each record refers to a different shipment. The Shipments table is related to an Orders table, which contains records pertaining to reorders of products. My end game is to be able to indicate which shipment a certain product is expected to arrive on...hence needing the drop down value list to reflect upcoming shipments.

                        We are running FM 11 with not current plans to upgrade. Converting my db's scares me.

                         

                        That is the relationship I am still using, with one added qualifier: 

                        Table::Country = Table::Country

                        It is a different date field. The date field used in the relationship is the expected date of departure, the date I need to use is the actual date of departure.

                        • 9. Re: Need Value List from Calc Field
                          philmodjunk

                          The difference is in whether you are entering new data into fields or using the selected value from the value list as criteria for a find. What happens next and in what viewing mode that it needs to work can be important details.

                          Let's see if I can fill in the details on your relationships:

                          Shipments::cYear = Shipments 2::cYear AND
                          Shipments::expected date of departure > Shipments 2::expected date of departure AND
                          Shipments::Country = Shipments 2::Country

                          That correct?

                          And you want to select from a list of values: "First" ; "Second" ; "Third" ; "Fourth" ; "Fifth" ; "Sixth" ; ...

                          in a field in a different table, Orders.

                          And you want to only list the values from shipments whose Shipments::actual date of departure is greater than today's date (ie a shipping date still in the future.)

                          Is there any other criteria here? And is selecting this value inteneded to link the order record to that specific shipment record? (Just entering the listed value won't do that, you should set it up so that selecting the value enters a ShipmentID number to link the two records.)

                          It's not impossible to set this up so that this functions as a drop down list, but keeping indexed values in place and up to date will not be nearly as simple as using a different method for selecting the value. Would it work to have a small portal on your layout where you see the correct list of values and clicking a row in the portal enters the ShipmentID into the current order record? If so, we can set up a filtered portal where we can display data from our unstored fields and not need to set up indexed fields solely to serve as the data source for your value list.

                          • 10. Re: Need Value List from Calc Field
                            letra

                            I see what you mean now...it is data entry, and should only need to be accessed in browse mode.

                            Yes, the relationships you outlined are correct.

                            Yes, I want to select from the list of values you mentioned in a different table, with the options limited by the dates in the way that you mentioned. I don't think there's any other criteria to worry about. And your premonition about linking the order records to a shipment record is also accurate.

                            The filtered portal may work, I think I should mention that the primary users of this db access it via IWP...

                            • 11. Re: Need Value List from Calc Field
                              philmodjunk

                              Always tell the forum up front when you will need to use IWP. IWP limits the possible solutions when it comes to interface design so it can be frustrating to come up with a suggest solution only to find out that the user needs to make it work with IWP.

                              In this case, I believe a filtered portal where each row in the portal is a button set to perform a script will still work with IWP.

                              Won't you need to limit your list of values by country as well as by date? That would seem reasonable given how the labels "first", "second", etc. are assigned in the first place.

                              Assuming that this is the case, set up this relationship to an occurrence of your Shipments table:

                              Orders::Country = ShipmentsByCountry::Country

                              Put a portal to ShipmentsByCountry on your orders layout and use this portal filter expression:

                              Get ( CurrentDate ) < ShipmentsByCountry::actual date of departure

                              Put your "label" field in the portal and you'll get a list of values: "Third", "fourth",... Etc in the portal for yet to be shipped shipments from the same country.

                              Select this field (and any others you may choose to put in the portal row), while in browse mode and select Button Setup... from the layout menu to turn it into a button.

                              Set it up to perform this script:

                              Set Field [Orders::ShipmentID ; ShipmentsByCountry::ShipmentID ]

                              Note: if you don't want to limit the values by country, this method still works with just a few modifications.

                              • 12. Re: Need Value List from Calc Field
                                letra

                                Yes, sorry about that. 

                                I will try your solution and let you know that it works.