FOR loop in calculated field

[Mariusz Materek]

Hi,

Could someone help me with creating calculated field which filter my table twice and then take specific value as value for field for example:

for ( field 1 = 1)

for ( field 2 = 2)

use field 3

How to create for loop in filemaker so i can nested Filter ( textToFilter ; filterText ) twice to filter table content and get field 3 as a result.

Any ideas?

Thanks!

[dansmith65]

I don't understand what you are trying to do, but there is no direct equivalent of a For loop in FileMaker as you would find in other languages, like PHP: http://php.net/manual/en/control-structures.for.php

I'm not sure, but I think you may be looking for the Case function: http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref3.33.2.html#1027634

[Vaughan]

Assuming that you mean:

if field1 = 1 and field2 = 2 then return the value of field 3

then the expression would be:

Case( field1 = 1 and field2 = 2 ; field 3 )

"How to create for loop in filemaker so i can nested Filter ( textToFilter ; filterText ) twice to filter table content and get field 3 as a result."

Not sure what you mean by that question. Can you explain your requirements better?

[Mariusz Materek]

Hi,

I was trying to use Filter function to filter table content, for example let say that we have table:

id field1 field2

1 1 a

1 2 b

1 3 c

2 1 a

2 2 b

2 3 c

I was trying to filter table ID by using Filter function, then i was trying to filter field1 by Filter function. last step was to assign field2 value to calculated field.

Some how by writing this:

Case ( Filter ( joint_seqsht_tools::seq_sht_id; seq_sht_id ) ;

Case ( Tools::Assy No = "1001" ; Tools::Desc ; "no tool" ))

it is working

Do you know any other ways for filtering or searching for informations when calculating field?

Thanks

[innodat]

I'm taking a blind shot here, because I don't really understand what you're asking... You can't filter table content, only field content. But maybe you're looking for this?

Case(

Filter (field1; 1) and Filter (field2; 2);

field3)

[Mariusz Materek]

it is giving me the right result same how

I'm at the moment in early stage of learning and designing so we will see

thanks a lot for help

[Mariusz Materek]

ups,

its is not working

as rheinport commented filter only works for fields not tables

i attached copy of my learning database

learning.fp7.zip

on SetupSht layout tool_1 should return Tools::Desc value if tool 1001 exist for specific SeqSht. so if I insert seq_sht_id = 1 on SetupSht layout tool_1 should return "no tool" as there is no record within joint_seqsht_tools where seq_sht_id = 1 and Assy No = 1001. for field_2 it should return "two" as there is a vaild record within joint_seqsht_tools table where seq_sht_id = 1 and Assy No = 1002.

in case of changing seq_sht_id = 2 both field_1 should return "one" and field_2 should return "two"

Hope I explain my problem clear enough

Thanks for all support

[Ocean West]

I see that you are referencing related data in that case you need to look at all the related records using List( ) function this will

I also changed the fields to a calculation - not knowing what your goal is that may or may not be desirable as it may be a performance hit.

Let ( x = PatternCount ( ¶ & ;  List ( Tools::Assy No ) & ; ¶ ; ¶ & ; 1002 & ; ¶  ) ; Case ( x ;  "two" ; "no tool" ) )

learning.fp7.zip

[Mariusz Materek]

magic!

thanks for that, I will try to figure it out tomorrow

thanks Ocean West

P.S. Why have to use "¶" in calculation?

[Mariusz Materek]

I can't sleep now... to excited

I tried to implement your solution with next field but some how its not working. Why with new field np number and the same calculation is not picking right values from table Tools::NP Number?

Thanks

learning.fp7.zip

[Ocean West]

The List function returns a set of values: 1001¶1002¶1006 and I am using pattern count to to give me a boolean result if that list contains the value we seek.

the plicrow is a return character - by wrapping the string and pattern with the pilcrows allow us to match if the item occurs in any position: ¶1001¶1002¶1006¶ does ¶1002¶ exist in the that list - True / does ¶1001¶ exist in that list - True / does ¶1003¶ occur - False,

I kind of see where you are going with this but I am sure that this is NOT the best scaleable solution as you would need to modify your table and schema and include EVERY possible product to determine if the product existed. It may be ok if you only have a few.

Is your goal to inform the data entry person if a particular value is in the join table? Or show Items that are NOT in the join file? Is this for a report of some sort?

Showing data that exists is easy - however when you are trying to report on NULL data or data that doesn't exist:

IE: if you want to confirm you had in your table cats, dogs, mice, fish - easy to confirm as values in a record(s) exist, however is it required that horses should be in that list you would need to have a value list or table of expected values in order to perform a comparison of all known expected items and only items that currently existed.

[Ocean West]

Looking further at your example it would't be possible to determine the correct - value of the Tools::NP Number because filemaker thru the relationship would only match the VERY first record in the Tools Table - and since the relationship is many to one - you would not see the desired result.

Here is another approach:

learning-1.fp7.zip

(i added a custom function that will find the missing values from the items in the list)

[Lee Smith]

Any ideas?

Thanks!

Please update your profile to reflect your current version of FileMaker, operating system and platform you're using.

is not that hard to do and should only take you a couple minutes to complete. It is important to us to know this information when replying to questions that you might have. It will help us narrow down which response would help you when it comes to these sort of items.

Start by going to the top right-hand part of the page, click on the little down arrow beside your name.

Select the My Profile from the list, which will then take you to your profile page.

You will then notice (at least in my browser) is a BIG black buttons labeled Edit my Profile, This will take you to the page where you can edit those things that need to be changed. When completed, simply save your changes. Let me know if you have a problem.

TIA

Lee

[Mariusz Materek]

Hi again,

The aim for this exercise is to:

1. Have a table "tools" with all tools available

2. Have sequence sheet where user add tools in order they are use (possible repetitions)

3. Automatically create setup sheet

By setup sheet I mean a list with constant number of tools 1001 - 1010 set always when creating new record.

Now, when tool 1001 doesn't exist on sequence sheet on setup sheet related field automatically say "no tool".

If tool 1001 exist (one,twice or more) on sequence sheet, data (and additional informations) for specific tool get copied / calculated or linked across to setup sheet.

So in essence I would like to pick relevant information for setup sheet from tools table only when specific tool exist on sequence sheet

Thanks for previous example and explanation.

P.S. Lee all info now updated!

[comment]

I am missing a table here: when you create a "sequence sheet where user add tools in order they are use" - this would a sequence of tools to be used for ... ? If I understand correctly, there should be a table of Procedures, so that:

Procedures -< Tasks >- Tools

where Tasks are the "sequence sheet" for each procedure.

With this in place, you can have a portal showing all tools (using the x relational operator) on a layout of Procedures, and use conditional formatting to indicate which tools are used in the procedure and which not. This is given by =

IsEmpty ( FilterValues ( AllTools::ToolID ; List ( Tasks::ToolID ) ) )

[Mariusz Materek]

Hi,

By using and analysing some examples from you guys I manage to get sort of solution I was looking for.

learning portals filter.fp7.zip

It will require to have lot of portals within one layout so the question is how FM will perform. However by setting separate portal for each tool on setup sheet and adding filter i manage to get right result.

Comment thanks for your suggestion I will test it next to see how it will work. I have noticed that I already have similar relationships between SeqSht -< joint_seqsht_tools >- Tools

Thanks for support!

[comment]

However by setting separate portal for each tool on setup sheet and adding filter i manage to get right result.

That's hardly the best approach; users should be allowed to add tools as necessary, without requiring layout adjustments.

.

[Mariusz Materek]

Yes, you right but I only want users to add tools by using sequence sheet. Setup sheet is just simplified (summary like) view in this case where tools are displayed only once and always in the same order 1001 first 1002 second.

Below you can find example of what I tried to achieve in first place. In this case when there is no tool 1001 on sequence sheet setup sheet display data from tool 0000. This mean that I have to have 0000 tool on every sequence sheet just in case there is no tool 1001. Is there a way to set portal to display default value if tool 1001 doesn't exist?

Thanks

learning portals filter with setup sht.fp7.zip

[comment]

I am afraid you have lost me there.