Filemaker won't Calculate Fields unless drop down used

[yilbber]

I have a lot of tables to fill, and I don't want to use a drop down menu to reselect all 3000 entries. Is there a better method to do this? I originally tried lookup but it refused to accept input, then I changed it to drop down, but now It won't auto lookup entries. Is there a way to force this behavior?

 

[eos]

I have a lot of tables to fill, and I don't want to use a drop down menu to reselect all 3000 entries

What does “fill tables” mean​? And what “reselect”? And why does the title say “calculate fields”?

Edited June 10, 2015 by eos

[comment]

I am only guessing here, but perhaps you meant a relookup?
http://www.filemaker.com/help/14/fmp/en/html/relational.12.22.html#1029105

[yilbber]

the field isn't defined as a lookup, so Filemaker doesn't want to look it up with relookup. When I define the field with a drop down value list, it refuses to update until i reselect the current text

I think the problem may be that i am using vlookup instead of the standard lookup option, but then i can't get the right information either..

 

[comment]

I think the problem may be that i am using vlookup instead of the standard lookup option, but then i can't get the right information either..

​I think the problem is that you're not explaining clearly what the problem is. For example, I have no idea what you mean by "using vlookup instead of the standard lookup option". There is no "vlookup" in Filemaker.

[bruceR]

Additionally, there is ambiguity about what you (ylibber) mean by "define a field with a dropdown value list".

Defining a field is done in field definitions, in manage database. You cannot specify interface control styles when defining fields.

That is done at the layout level.

[yilbber]

vlookup is a custom function, that behaves like a vertical lookup from Excel. When i use a drop down list on a Text field, The fields it looks up does not get sent any input until i run the dropdown. the fields remain blank. even if they have text already in them.

which makes my job worse because now i have to perform those lookups externally when i import my data.

 

[yilbber]

This is what Vlookup does (

// Created 2014-02-03 by Doug Staubach  -- This file is version 1.0 -- http://www.briandunning.com/cf/1740
// Simulates the VLOOKUP function in Excel -- looks in a specific column for an exact match and if a match is found, returns the value from a different column on the same row (in the same table)
If (
     IsEmpty ( MatchingValueOrList ) ; ""                                                                                 //exit if there are no more items to process
     ;Let ([
            VAR1 = GetFieldName ( LookupTableNameAndFieldName )
           ;VAR2 = LeftValues ( MatchingValueOrList ; 1)                                                          // you can lookup a single value, or a list of values
           ;VAR2 = If ( Right ( VAR2 ; 1 ) = "¶" ; Left ( VAR2 ; Length ( VAR2 ) - 1 ) ; VAR2 )      // gets rid of the trailing "¶" character
           ;VAR3 = GetFieldName ( ReturnFieldName )
           // -------
           ;FLD1 = "\"" & GetValue ( Substitute ( VAR1 ; "::" ; "¶" ) ; 2 ) & "\""                             // get the first field's current name and format it for SQL-safe processing
           ;FLD2 = "\"" & GetValue ( Substitute ( VAR3 ; "::" ; "¶" ) ; 2 ) & "\""                             // get the second field current name and format it for SQL-safe processing
           ;TBL1 = "\"" & GetValue ( Substitute ( VAR1 ; "::" ; "¶" ) ; 1 ) & "\""                             // get the TABLE name and format it for SQL-safe processing
           ;TYP1 = MiddleWords ( FieldType ( Get ( FileName) ; VAR1 ) ; 2 ; 1 )                          // get the lookup field's data type (Text, Number, Date, Time, Timestamp, or Container)                                    
           // -------
          ;MyCmd =
             "SELECT (" & FLD2 & ")" &
             " FROM " & TBL1 &
             Case (
                 TYP1 = "Text" ; " WHERE " & FLD1 & " = " & "('" & GetAsText ( VAR2 ) & "')"          // text has to be enclosed in single quotes
                ;TYP1 = "Number" ; " WHERE " & FLD1 & " = " & ( GetAsNumber ( VAR2 ) )            // numbers (and other types?) don't need quotes
              )                                                                                                                            // room for expansion here (need to add the other 4 types)
          ;VAR4 = ExecuteSQL ( MyCmd ; "" ; "" )
        ];
        If ( Trim ( VAR4 ) ≠ "?" ; Trim ( VAR4 ) ; "" )
     )
     & " " & Vlookup ( LookupTableNameAndFieldName ; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ; ReturnFieldName )   // recursion (move to next item and go again)
)
// Uses ExecuteSQL, but you don't have to know anything about SQL to use it
// Tested with FileMaker Pro v13 (this might also work with FileMaker Pro v12 -- main requirement is the ExecuteSQL function)
// Example1: VLookup ( TableInventory::ItemNumber ; "007¶626¶" ; "Description" ) = "Lamp¶Television¶"
// Example2: VLookup ( TableInventory::Description ; "Lamp" ; TableInventory:ItemNumber ) = "007¶"
// Example3: VLookup ( TableInventory::ItemNumber ; List ( TableInvoices:ProductsPurchased ) ; TableInventory:Description ) = "Cigar¶Rum¶Recliner¶"

when ever i have a field that uses this and the field referred to is for example , itemnumber, the script does NOT run if the Field it calls up, (itemnumber) is turned into a valuelist drop down. it only runs after the valuelist was selected by mouse