2 Replies Latest reply on Sep 16, 2015 3:27 PM by lbl159

# Issue with GetValue/GetField functions

Below is my function. The Series Order Distribution portion has many other parts, but I didn't include them here because of length. It is highlighting S3_Liquidation Preference Value 1 saying that an operator is expected here. Any ideas on how to fix this formula?

I am trying to reorder information based on the Financing Round (of which I have 1-10).  Basically saying if the Financing Round Order is 1 then take the value from S3_Liquidation Preference Value 1, if the Financing Round Order is 2 then take the value from S3_Liquidation Preference Value 2

Let([

Series Order Distribution =

If ( S3_Investments_Entities::S3_Financing Round Order 1 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 2 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 3 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 4 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 5 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 6 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 7 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 8 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 9 = S3_Investments_Entities::S3_Financing Round 1 ; 1 ) &

If ( S3_Investments_Entities::S3_Financing Round Order 10 = S3_Investments_Entities::S3_Financing Round 1 ; 1 );

result =

GetValue ( "GetField ( "S3_Investments::S3_Liquidation Preference Value 1" )

GetField ( "S3_Investments::S3_Liquidation Preference Value 2" )

GetField ( "S3_Investments::S3_Liquidation Preference Value 3" )

GetField ( "S3_Investments::S3_Liquidation Preference Value 4" )

GetField ( "S3_Investments::S3_Liquidation Preference Value 5" )

GetField ( "S3_Investments::S3_Liquidation Preference Value 6" )

GetField ( "S3_Investments::S3_Liquidation Preference Value 7" ) "

; Series Order Distribution )] ; result )

• ###### 1. Re: Issue with GetValue/GetField functions

lbl159 wrote:

Below is my function. The Series Order Distribution portion has many other parts, but I didn't include them here because of length. It is highlighting S3_Liquidation Preference Value 1 saying that an operator is expected here. Any ideas on how to fix this formula?

You are using quotes where none are needed – GetField() is only useful if you pass a calculated string as a field reference; direct references are evaluated anyway.

But the best way to fix this formula would be to not have to write it.

It has been suggested several times in the posts you made, but let me repeat it:

You should create a new table where each of these sets of numbered fields is an unnumbered set in a single record, and the number/index is an attribute of that record.

This lets you create relationships and define formulae that calculate the correct results per instance, regardless of their number.

That being said, for now try

Let ( [

financingRound1 =

S3_Investments_Entities::S3_Financing Round 1 ;

financingRoundOrderList =

List (

S3_Investments_Entities::S3_Financing Round Order 1 ;

S3_Investments_Entities::S3_Financing Round Order 2;

S3_Investments_Entities::S3_Financing Round Order 3 ;

S3_Investments_Entities::S3_Financing Round Order 4 ;

S3_Investments_Entities::S3_Financing Round Order 5 ;

S3_Investments_Entities::S3_Financing Round Order 6 ;

S3_Investments_Entities::S3_Financing Round Order 7 ;

S3_Investments_Entities::S3_Financing Round Order 8 ;

S3_Investments_Entities::S3_Financing Round Order 9 ;

S3_Investments_Entities::S3_Financing Round Order 10

) ;

index =

ValueCount (

Left (

financingRoundOrderList ;

Position (

¶ & financingRoundOrderList ; ¶ & financingRound1 ; 1 ; 1

)

)

)

] ;

GetField ( "S3_Investments::S3_Liquidation Preference Value " & index )

)

Note that the index is the first match in the list. Also note all the repetitions …

Should you ever add more financing rounds, you need to painstakingly adjust all these calculations – and still have a flawed data model …

• ###### 2. Re: Issue with GetValue/GetField functions

Thank you very much I appreciate the help greatly.  The problem that I am having is I started working with this database (with no FMP/Database experience) and it was already designed like this.  I would have to redo the structure of such a large percentage of the database to fix this issue throughout.

I really appreciate everyone's help with this. Most of the time I have a good general idea how things work, but some specifics are tough because I don't have the knowledge base.  From now on I am going to be working off-site on a couple of large systems for this database and I will go through and get some work done on cleaning everything up.