Attempting multiple if & else if statements in script

[rt916]

Hey folks,

I am working on a layout that auto-populates a bunch of data from related fields. One of the fields is called district_number which has the numerical 1-12 value, depending on the district. I am trying to get a script to read the district number, then based on the value have it populate data in another field.

I started by setting the district_number as a variable, $DistrictNumber

Then I have the If statement:

If[$DistrictAddress = "01"]

  Insert Text [Select;xxx::district_address_label; "District 1 P.O. Box 3700 blah blah blah"]

Else If [$DistrictAddress = "02"]

  Insert Text [Select;xxx::district_address_label; "District 2 P.O. Box 3700 blah blah blah"]

Else If [$DistrictAddress = "03"]

  Insert Text [Select;xxx::district_address_label; "District 3 P.O. Box 3700 blah blah blah"]

... etc...

End If

But ti doesn't work. Any help on what I am doing wrong is much appreciated!!

Thank in advance,

RT

here is an image of how it looks

Edited April 27, 2017 by rt916

[Lee Smith]

What is meant by? 

39 minutes ago, rt916 said:

But ti doesn't work

 

[comment]

34 minutes ago, rt916 said:

But ti doesn't work.

"doesn't work" is not a good description a problem. What - if anything - actually happens?  Most likely, the value of the variable is not what you think it is (esp. since you say it's "numerical" - numerical values do not have a leading zero). 

Anyway, it seems your task would be much better accomplished by performing a lookup from a table of 12 records, with fields for Code (a number between 1 and 12) and Address (the actual text you're trying to insert).

 

[rt916]

I apologize for the typo!

I looked in SQL and the district_number field is a variable character field. 

All of the other script functions work and it finishes my new layout with the other fields populated, but the district_address_label is blank.

To run this as a lookup, I would setup a new table with 1-12 numeric values? and another address field/column with the actual text to insert?

[comment]

1 minute ago, rt916 said:

I looked in SQL and the district_number field is a variable character field. 

You should look in Filemaker's File > Manage Database… > Fields, under Type. And since you have the Advanced version, you can use the Script Debugger and the Data Viewer to see what the actual value of the variable is at script runtime, and what does the test return.

 

6 minutes ago, rt916 said:

To run this as a lookup, I would setup a new table with 1-12 numeric values? and another address field/column with the actual text to insert?

Yes. And a relationship based on matching the numerical code. Note also that you need a lookup only if you want to store a copy of the looked up address. Otherwise you can just display the related address directly on your layout.

 

[rt916]

Ok got it, thank you for the help! I will make the changes and test doing it that way.

Unfortunately I only work on this job Tues-Thurs so if I am not finished today I may ask for more advice next Tues. But the help is much appreciated, have a great weekend!

[OlgerDiekstra]

Insert text requires the field to be on the layout (xxx::district_address_label). Check that it is.

Why use insert text though? Why not use set field?

[siroos12]

Setting a field or a variable using "Case" function would be another option.

[rt916]

On 5/2/2017 at 10:25 AM, siroos12 said:

Setting a field or a variable using "Case" function would be another option.

I like the idea of setting this up as a Set Field step in a script, but how would the calculation look for that?

I specified the target field (adl_tool::district_address_label) and set the target field with the "text address I want to insert" in the calculation, but the ayout page with the _district_address_label is still coming out blank. I attached the screenshot with only the 1st district changed. Please see attached and let me know what I'm doing wrong.

 

[siroos12]

Just now, rt916 said:

I like the idea of setting this up as a Set Field step in a script, but how would the calculation look for that?

I specified the target field (adl_tool::district_address_label) and set the target field with the "text address I want to insert" in the calculation, but the ayout page with the _district_address_label is still coming out blank. I attached the screenshot with only the 1st district changed. Please see attached and let me know what I'm doing wrong.

 

 

In order tu use "Case()" you need to set a variable and call it something like "$DistrictAddressLabel" and use the case as below:

Case ( $DistrictAdrees = 1; 
       "District 1 P.O. Box 3700 blah blah blah";

       $DistrictAdrees = 2;   
       "District 2 P.O. Box 3700 blah blah blah";

       $DistrictAdrees = 3;   
       "District 3 P.O. Box 3700 blah blah blah";
//And so on...
       )

 

Then set the field as below:

 

set field [ADL_Tool::District_Address_Label ; $DistrictAddressLabel]

 

 

Edited May 3, 2017 by siroos12

[comment]

On 5/2/2017 at 8:25 PM, siroos12 said:

Setting a field or a variable using "Case" function would be another option.

There is no real difference between using the Case() function and a block of  If - Else if - End If script steps. The logic is the same - and so is the flaw of hardcoding data into the schema. And the reason why one doesn't work is probably the same reason why the other doesn't work either - but without a reproducible example we can only speculate about that.

[siroos12]

Just now, rt916 said:

I like the idea of setting this up as a Set Field step in a script, but how would the calculation look for that?

I specified the target field (adl_tool::district_address_label) and set the target field with the "text address I want to insert" in the calculation, but the ayout page with the _district_address_label is still coming out blank. I attached the screenshot with only the 1st district changed. Please see attached and let me know what I'm doing wrong.

 

Check the script step "Go to layout" at the beginning. The layout you selected has a different table source as the fields you are setting. Layout is based on TO called "CentralENV" while you are setting field of TO called "ADL_Tool".

 

Isn't that the problem?

Just now, comment said:

There is no real difference between using the Case() function and a block of  If - Else if - End If script steps. The logic is the same - and so is the flaw of hardcoding data into the schema. And the reason why one doesn't work is probably the same reason why the other doesn't work either - but without a reproducible example we can only speculate about that.

I am agreed with you. the reason why I use Case() is that it just simplifies the script and make it shorter so debugging is easier.

And again, Agreed, we need to see the file and play around it to have a better understanding of the situation. 

[rt916]

CentralENV and ADL_Tool are related TO's, and all the other fields that I reference populate fine. Maybe I need to populate the district_address_block in the ADL_Tool TO first in the script step, then open the layout ADL_Print_Template later in the script?

[siroos12]

Can you upload a clone copy of the file for us?

[rt916]

I don't think so, the file is massive and has literally over 10,000 active projects with tons of PII data. I'd probably get in a lot of trouble if someone found out I shared it! 

[bruceR]

The question was about uploading a clone. Clones have no data.

[siroos12]

Just now, rt916 said:

I don't think so, the file is massive and has literally over 10,000 active projects with tons of PII data. I'd probably get in a lot of trouble if someone found out I shared it! 

When you save a file as a clone copy, it removes all the records of all tables.

[comment]

It would be much better to reproduce the problem (and nothing else) in a new file, with some fake data.

[rt916]

5 minutes ago, siroos12 said:

When you save a file as a clone copy, it removes all the records of all tables.

I manage this database for a gov agency and there is a very real fear that if the public knew that the db existed they would overwhelm the agency with public information requests. Plus I would have to get authorization from my superiors to upload/share it, and I know the answer to that would be a resounding no! I'm not trying to be difficult, it's just the reality in gov info sharing :/

[siroos12]

I understand your situation. I hope that other folks will be able to help you on this issue.

The other option is to employ a FM developer and ask him/her to sign a NDA for you to make sure that no data will be shared to public.

Good luck.

Edited May 3, 2017 by siroos12

[rt916]

1 hour ago, comment said:

There is no real difference between using the Case() function and a block of  If - Else if - End If script steps. The logic is the same - and so is the flaw of hardcoding data into the schema. And the reason why one doesn't work is probably the same reason why the other doesn't work either - but without a reproducible example we can only speculate about that.

Well your right, I keep running into problems hardcoding data in the schema and it's probably considered best practice to separate the data from the schema. So I setup a related TO& Layout with the address field as text (varchar) and district numbers (numerical) and added the data in there. Now I have a district number field (1-12) and address block with the text I want inserted in my template, sounds good so far.

So to make the relationship work I need to add another (numerical) field to the CentralENV table for the district number and connect them in the graph? 

[comment]

22 minutes ago, rt916 said:

So to make the relationship work I need to add another (numerical) field to the CentralENV table for the district number

I thought you already had such field:

On 4/27/2017 at 11:11 PM, rt916 said:

One of the fields is called district_number which has the numerical 1-12 value, depending on the district. I am trying to get a script to read the district number, then based on the value have it populate data in another field.

 

[rt916]

4 minutes ago, comment said:

I thought you already had such field:

 

I have one in the ADL_Tool table, which grabs the district number from the CentralENV (varchar) table and saves it as a numerical value. I added a 2nd field with the address text and connected them in the relationship graph via the district number. Now I need to populate the district_address_label with the corresponding address text from the related district_number, of which there are 13.

I apologize for the confusion! I have been learning this db that 10-15 people setup over the course of 12+ years that nobody cared to ever document! Now everyone is gone and I'm trying to implement new workflows and make the system better, and it's a rats nest to say the least. 

[comment]

I am sorry, but I don't know what your table names mean and what do they represent in real life. I understand you have a table where a user will populate a district_number field with a numerical value. Let us call this table Target. Now you have added new table (not just a new TO of an existing table) with 2 fields and 12 records, something like:

DistrictNumber   AddressLabel
1                District 1 P.O. Box 3700 Eureka CA
2                District 2 1657 Riverside Drive Redding CA
...
12               District 12 1750 East 4th Street Santa Anna CA

Let us call this table Districts. 

The next step is to define a relationship as:

Target::district_number = Districts::DistrictNumber

and have the district_address_label field in the Target table lookup its value from Districts::AddressLabel.

As I mentioned before, this is assuming you need to copy the address label into the Target table - thus recording what the address was at the time of the lookup (i.e. protecting it from future changes). Otherwise you can just display the related address label from the Districts table on the layout of the Target table.

 

[rt916]

16 hours ago, comment said:

I am sorry, but I don't know what your table names mean and what do they represent in real life. I understand you have a table where a user will populate a district_number field with a numerical value. Let us call this table Target. Now you have added new table (not just a new TO of an existing table) with 2 fields and 12 records, something like:

DistrictNumber   AddressLabel
1                District 1 P.O. Box 3700 Eureka CA
2                District 2 1657 Riverside Drive Redding CA
...
12               District 12 1750 East 4th Street Santa Anna CA

Let us call this table Districts. 

The next step is to define a relationship as:

Target::district_number = Districts::DistrictNumber

and have the district_address_label field in the Target table lookup its value from Districts::AddressLabel.

As I mentioned before, this is assuming you need to copy the address label into the Target table - thus recording what the address was at the time of the lookup (i.e. protecting it from future changes). Otherwise you can just display the related address label from the Districts table on the layout of the Target table.

 

I got it working!! I used your recommendation for entering the district numbers and addresses then created the address block as a portal, sorted by district_number, and it worked!

Your help and input is much appreciated, thank you guys! I'm so happy this forum is here! 

[comment]

1 hour ago, rt916 said:

then created the address block as a portal, sorted by district_number

I am not sure why you need a portal (and even less sure why it needs to be sorted), when there will be at most one related record.

[rt916]

4 hours ago, comment said:

I am not sure why you need a portal (and even less sure why it needs to be sorted), when there will be at most one related record.

you are correct, I was overthinking it. the TO with the related field works just fine. Less work and cleaner workflow! thanks "comment"!