If Then Else Or Case?

[David65]

Hi! For a field I need the following sort of thing to work:

Case (

TableA::ThisYear = "Yes" and not IsEmpty ( TableA::Field1 ) and not IsEmpty ( TableB::Field2 ) and TableA::Field1 = TableB::Field2;TableA::Field1;

TableA::ThisYear = "Yes" and not IsEmpty ( TableA::Field1 ) and not IsEmpty ( TableB::Field2 ) and TableA::Field1 ≠ TableB::Field2; TableA::Field1 & "" & TableB::Field2)

It's all text, trying to get input from two layouts (single field each) to be added to a third layout in a single field if a Yes radio button is checked. If the text exists and is identical from both then I only want one shown, if they differ then I'd like them listed with commas between them. I'd really appreciate any direction. I'm obviously new to FM and am probably missing something basic so thanks a lot in advance. I'm trying Case and If statements but can't seem to get either to work.

[comment]

Try =

Case ( 

TableA::Field1 = "Yes" and not IsEmpty ( TableA::Field2 ) and not IsEmpty ( TableB::Field2 ) ;

TableA::Field2 & ", " & TableB::Field2 ;



TableA::Field2 = TableB::Field2 ;

TableA::Field2

)

Keep in mind that a reference to a field in another table uses data from the first related record in the other table.

[David65]

Thanks. It doesn't work and it's probably cause I'm working with portals in tabs in the same layout(?)-

I have portals on all three tabs. In more detail:

Tab 1 (tabbed portal)- portal contains related fields only from TableA, fields outside portal are from TableD (the table common to TablesA, B & C)

Tab 2 (tabbed portal)- portal contains related fields only from TableB, fields outside portal are from TableD (the table common to TablesA, B & C)

Tab 3 (tabbed portal)- portal contains related fields only from TableC, fields outside portal are from TableD (the table common to TablesA, B & C)

So I have a field in Tab 3 which exists in TableC that I would like to populate with data from an existing field in each portal in Tabs 1&2 IF Yes is selected for each record and data exists in either of the existing fields in question in the two portals.

Visually:

~~~~~~~~~~~~~~

Tab 1

....TableD fields...

_____portal_______

TableA::Field1 TableA::FieldA TableA::FieldB TableA::ThisYear [Yes No]

...rows

_____portal_______

....TableD fields...

~~~~~~~~~~~~~~

~~~~~~~~~~~~~~

Tab 2

....TableD fields...

_____portal_______

TableB::Field2 TableB::FieldX TableB::FieldY TableB::ThisYear [Yes No]

...rows

_____portal_______

....TableD fields...

~~~~~~~~~~~~~~

~~~~~~~~~~~~~~

Tab 3

....TableD fields...

_____portal_______

TableC::Field1 TableC::FieldK TableC::FieldL...

...rows

_____portal_______

....TableD fields...

~~~~~~~~~~~~~~

So all my tabs are part of TableD because all data outside their portals is from TableD (TableD is the largest and central table of all).

I need TableC::Field1 (above) to show data under certain conditions from TableA::Field1 and/or TableB::Field2 (above) IF either of their ThisYear values are Yes.

Does that make sense? I've tried calculations but I'm doing something wrong that I don't see. Users will enter data in Tabs 1&2 and view it combined row to row in the portal on Tab 3. Each new row in either Tabs 1&2 that have data and are checked Yes should then be combined in the field in a new row on the portal on Tab 3....

I really appreciate your help. This is the last and toughest part of this project (of course). I'd be happy to pay some for help as well if needed (I'm behind schedule...).

[comment]

I'm afraid I cannot follow this at all. You are describing your visual setup, which is of no consequence here. What matters is the tables that you have and the relationships between them.

I suggest you abandon the cryptic "TableA::Field1" names and explain what this is really about. As I already hinted in my earlier post, I suspect the entire concept may be based on a false premise: it seems you have multiple "child" tables and you expect each child record to somehow pickup a "corresponding record" in another child table - but I am not sure on what basis.

If the only relationship between the child tables is through the "parent table" (D), then this won't work: any reference to Table A made in Table C will use data from the first record in A - no matter which record in C is doing the calculation.

[Genx]

@comment

If the only relationship between the child tables is through the "parent table" (D), then this won't work: any reference to Table A made in Table C will use data from the first record in A - no matter which record in C is doing the calculation.

(Purely academicaly)

That's not entirely true... you could use GetNthRecord to target the non first related record in the list.

If you wanted to determine the corresponding record number you could do that using an unstored calc - List(TableC::UniqueID) from the parent table (D) in this case and then use some basic positioning functions to determine what the index of the related record is from the perspective of the parent record. Using that info you could then calculate with the corresponding nth record values (from table C) via the parent table (D) relationship to table B or A.

Ignore me if that made no sense... having a tough day lol.

@OP:

TableA,TableB,TableC,TableD

It's really frustrating when you try to explain an abstract concept like this. What you are describing you want to achieve (even if it is acheivable) may be getting implemented entirely the wrong way because you're not describing the point of anything you're doing. It'll take you 10 times longer to get a useful answer out of anyone with this. I can understand you're trying to keep out irrelevant facts, but your description is almost incomprehnsible as a result.

[David65]

I thought I was describing what I'm looking at and their relationships. I've inherited this project, it's a FileMaker 11 database with 23 tables created from converting and combining 15 FileMaker 5 datababases so I was trying to simplify things.

In the Relationships window I have the following involved tables:

Applications table (tbl D) is related to LFT table (tbl A) via primary key = foriegn key AND a constant = constant (LFT table has both Allow creation of records in this table via this relationship and Delete related checked)

Applications table (tbl D) is related to ONC table (tbl B ) via primary key = foriegn key AND a constant = constant (ONC table has both Allow creation of records in this table via this relationship and Delete related checked)

Applications table (tbl D) is related to PlanDetail table (tbl C) via primary key = foriegn key (PlanDetail table has both Allow creation of records in this table via this relationship and Delete related checked)

It sounds like you're right, our previous consultant disappeared before finishing it and I've had to do a lot of renaming and tweaking to get basic things working properly. Almost everything now does work except this. None of the Tables (the LFT table, ONC table or the PlanDetail table) show relationships between them (only with the Applications table). How would I fix it so they could do what I'm looking to do? Feel free to send me a url and I'll take it from there if you prefer. I'm pretty new to this level of db work as you can tell... I really appreciate your patience.

[Genx]

Can you substitute out the acronym LFT and ONC in the description above for what they actually mean and redescribe.

E.g.

When a new Plan is added to an application, I want FileMaker to copy the details from the corresponding LFT (?) record if the information is available...

[David65]

Those are the actual names of the tables...

OK I think I understand what you need. How about this -

I have a layout with lots of tabs on it. The layout has fields from the Applications table at the top and bottom.

Each tab has a portal in it that contains related fields from other tables.

I'm trying to take 1) data entered in a field in one tab (but only if a Yes is selected in another field in the same record) and/or 2) the data in second tab (but again only if Yes is selected in another field in the same record) and combine them in a field in a third tab which is also in a portal displaying fields from a different table.

So I have 3 tabs with 3 portals, each with its own table shown in a layout that displays fields from the Applications table (which the 3 tables shown in the portals are all related to). Users will enter the data in Tabs 1 & 2 only, Tab 3 will simply show that data in a combined form....

I can upload screen shots or whatever else would help. I'll look forward to your reply.

[David65]

When data is entered into Onc::ImplementationUnit and Onc::ThisYear = "Yes" and/or data is entered into LFT::IUName and LFT::ToBeTreatedThisYear = "Yes" put that data together separated by a comma in PlanDetail::AdminUnitName or if they are equal put it only once. This needs to happen for each row in the portal for Tab 3 corresponding to the combined number of rows with data in Tabs 1&2.

Does that make more sense?

[Genx]

Okay you're still missing a critical element though.

There are lots of records in the portals of each tab (the tab's are irrelevant for the purposes of the explanation fyi - they don't have any affect on anything).

Take the following scenario:

===========================

Portal 1 has 3 records

Portal 2 has 4 records

Portal 3 has 3 records

(We'll ignore portal 2 for a second).

Portal 1 has the following records:

=============================

Record #,Field1,Field2,Boolean

--------------------------------------------

1,"A","B","Yes",

2,"","","Yes",

3,"A","B","No"

Portal 3 (Ignoring portal 2)

============================

Record #, Should it pull a value from portal 1?

1,Yes

2,No

3,No

Is the above correct?

[comment]

None of the Tables (the LFT table, ONC table or the PlanDetail table) show relationships between them (only with the Applications table). How would I fix it so they could do what I'm looking to do?

As Genx explained, this is not telling us much. We need to understand what these tables represent in real life. Only on this basis one can derive the required relationships.

you could use GetNthRecord to target the non first related record

True, but I am making a point in a given context - not testifying under oath...

More to the point, I would say that - in general - such arrangement is merely a poor substitute to having ONE child table.

[David65]

Got it.

Portal 1 has the following records:

=============================

Record #,Field1,Field2,Boolean

--------------------------------------------

1,"A","B","Yes",

2,"","","Yes",

3,"A","B","No"

Portal 3 (Ignoring portal 2)

============================

Record #, Field1 above should be shown IF Yes in above so if the above then:

1,"A"

2,

3,

[Genx]

Now to the next point and to address comment's point:

Can Portal 1 / 2 / 3 have different number's of record's in them?

[David65]

Portal 3 will only shows records from Portal 1&2 so it'll only ever have the same number as the max number of records in either Portal 1 or 2 (depending upon which are combined).

Portal 1 & 2 will have varying numbers of records and Portal 3 will show as above.

But only one field from either Portal 1 or 2 will be brought over to Portal 3 (and only IF Yes...)

[Genx]

Two questions:

But if the corresponding records in portal 1 and 2 both exist and meet the requirements then they should both be brought accross into a single comma concatenated field?

What if the value in portals 1 and 2 change down the track (or they are changed to meet the requirements) AFTER the record has been created in portal 3 and the value has been brought across?

[David65]

Two questions:

But if the corresponding records in portal 1 and 2 both exist and meet the requirements then they should both be brought accross into a single comma concatenated field?

What if the value in portals 1 and 2 change down the track (or they are changed to meet the requirements) AFTER the record has been created in portal 3 and the value has been brought across?

Yes (to that field in Portal 3, one record to one record)

It should also be updated.

It's very likely that new records will also be added to Portal 1 & 2 over time as well.

[Genx]

Okay that last answer didn't really make any sense - can you just "Yes" or "No" to question 1 and 2 individually?

The question isn't whether or not records will be added to portal 1 and 2, the question is whether a change in the existing records in portal 1 / 2 corresponding to a record of the same index in portal 3 (including the addition of a corresponding record to portal 1 or 2 where at the time of the creation of a record in portal 3 there was previously no corresponding record in either portal 1, 2 or both) should effect the value previously brought across to portal 3.

[David65]

But if the corresponding records in portal 1 and 2 both exist and meet the requirements then they should both be brought accross into a single comma concatenated field?

ANSWER: Yes

What if the value in portals 1 and 2 change down the track (or they are changed to meet the requirements) AFTER the record has been created in portal 3 and the value has been brought across?

ANSWER: Then the value should also be updated in Portal 3.

[Genx]

Okay, there is a way to do this but it's not awfully clean:

1) Script triggers

2) Unstored Calculations.

As such, 2 questions:

The method you choose depends on what you're planning on doing with the data in portal 3 - is it just for displaying in this layout in this portal (viewing one application at a time) or are you planning on doing reporting and searching on this data ("This data" being the field that brings data across from portals 1 and 2 in portal 3)?

Is the data entry for portal's 1, 2 and 3 going to happen only on this layout?

[comment]

If I were playing this game, I would ask what should happen when a record is deleted. Say you have 4 records in both input tables (A and , and thus 4 records in the results table C.

C1 is a function of A1 and B1;

C2 is a function of A2 and B2;

C3 is a function of A3 and B3;

C4 is a function of A4 and B4.

Now let's delete record B2. Does this mean that C2 should now become a function of A2 and B3?

[David65]

Well no wonder he didn't get to it....

Regarding your questions:

1) The method you choose depends on what you're planning on doing with the data in portal 3 - is it just for displaying in this layout in this portal (viewing one application at a time) or are you planning on doing reporting and searching on this data ("This data" being the field that brings data across from portals 1 and 2 in portal 3)?

>> We need the data in Portal 3 to be available for reports, etc. so it needs to be stored in a field.

2) Is the data entry for portal's 1, 2 and 3 going to happen only on this layout?

>> Data entry effecting this field will only happen on Portal 1 & 2 on this layout. The data field in Portal 3 will be "view only" to users.

I really appreciate your help.

[Genx]

If I were playing this game, I would ask what should happen when a record is deleted. Say you have 4 records in both input tables (A and :, and thus 4 records in the results table ©.

C1 is a function of A1 and B1;

C2 is a function of A2 and B2;

C3 is a function of A3 and B3;

C4 is a function of A4 and B4.

Now let's delete record B2. Does this mean that C2 should now become a function of A2 and B3?

It's a fair question. The thought crossed my mind before, but on the grounds of the descriptions given so far I would presume the following:

Assuming that the number's you've used are representive of the unique id's of the records (as opposed to the related record's index), that the answer would be yes. David can you confirm?

Start

==============================

C1 is a function of A1 and B1;

C2 is a function of A2 and B2;

C3 is a function of A3 and B3;

C4 is a function of A4 and B4.

End

==============================

C1 is a function of A1 and B1;

C2 is a function of A2 and B3

C3 is a function of A3 and B4;

C4 is a function of A4

[David65]

If I were playing this game, I would ask what should happen when a record is deleted. Say you have 4 records in both input tables (A and :, and thus 4 records in the results table C.

C1 is a function of A1 and B1;

C2 is a function of A2 and B2;

C3 is a function of A3 and B3;

C4 is a function of A4 and B4.

Now let's delete record B2. Does this mean that C2 should now become a function of A2 and B3?

I'm glad you're not actually but I'll bite:

First of all, your As, Bs and Cs are all field values not records. Therefore, if you delete B2 you'd be left with A2.

Cheer up, it's the holidays!

[Genx]

Actually david the question does relate to the records and is fair (comment is trying to help, he is extremely generous with his time, he know's his stuff, and has helped almost everyone on this board (including myself) on many an occassion, so no need to get narky).

Are you saying that records will never be delete from portal 1 or 2 / aka A or B?

[comment]

First of all, your As, Bs and Cs are all field values not records. Therefore, if you delete B2 you'd be left with A2.

Huh?

[David65]

If complete records are deleted from Portals 1 or 2 then the conditions necessary (those fields wouldn't exist) for inclusion on Portal 3 would not pass therefore yes they should be deleted. My first concern was figuring out how to display the data necessary after entered, I was going to worry about clean up after that was working....

Sorry to be narky, whatever that means.

[Genx]

Must be an australian thing - narky is a nice way of saying angry. (it's like sarky which is a nice way of saying sarcastic)

Okay, so the solution would seem to be script triggers. You'd have to capture script trigger for changes in the related portal values and then either control deletion of records from portal 1 and 2 via a button and script on each related record or instead fire another trigger on commit of the main record to just re-apply all the values.

I can't look at this at the moment so maybe one of the other guys sitting on the thread might help you. If not, I'll take a look at it tommorow.

[comment]

Everything you said so far points to A, B and C being fields in the same record, in the same table.

However, this is a "technical" answer, given without truly understanding the subject. Giving directions when you don't know the destination is not really productive.

[David65]

I'm headed your way next week, small world. I was actually being more sarky then....

I'll look into script triggers more then, thanks. I already have record deletion via button scripts in Portal 1 & 2 so no worries.

Catch ya later comment. Thanks for your help too.

[Genx]

Everything you said so far points to A, B and C being fields in the same record, in the same table.

However, this is a "technical" answer, given without truly understanding the subject. Giving directions when you don't know the destination is not really productive.

Except for the statement made that Portals 1,2 and 3 can have varying numbers of records.

[David65]

Everything you said so far points to A, B and C being fields in the same record, in the same table.

However, this is a "technical" answer, given without truly understanding the subject. Giving directions when you don't know the destination is not really productive.

Actually A, B and C are all different fields in different records in different tables but I see how it looked like that. Take care.

[comment]

Except for the statement made that Portals 1,2 and 3 can have varying numbers of records.

I am not entirely comfortable defending this position, because I still don't know what this is about. However, the "statement made that Portals 1,2 and 3 can have varying numbers of records" doesn't hold when you consider that deleting record B2 in my earlier example does NOT shift records B3 and B4 upwards (at least that's how I interpreted the reply).

To put it more simply: when B2 is deleted, there remains a "null placeholder" for the missing values, and this placeholder must be used in the formula calculating C2 instead of the now missing B2.

This COULD be achieved by having a one-to-one relationship A - B - C (based on serial ID?), but I can see no justification for such convoluted approach (consider, for example, what would it take to re-instate B2 if needed).

I believe the correct implementation is a single table with the following limitation:

You cannot delete a record if either A or B holds values. "Deleting" B while A still has values should result in setting all fields in the B group to empty (and vice versa).

[Ron Cates]

Need a user and password?

[Ron Cates]

Yep. Sorry. Got it.

[Genx]

I am not entirely comfortable defending this position, because I still don't know what this is about. However, the "statement made that Portals 1,2 and 3 can have varying numbers of records" doesn't hold when you consider that deleting record B2 in my earlier example does NOT shift records B3 and B4 upwards (at least that's how I interpreted the reply).

To put it more simply: when B2 is deleted, there remains a "null placeholder" for the missing values, and this placeholder must be used in the formula calculating C2 instead of the now missing B2.

This COULD be achieved by having a one-to-one relationship A - B - C (based on serial ID?), but I can see no justification for such convoluted approach (consider, for example, what would it take to re-instate B2 if needed).

I believe the correct implementation is a single table with the following limitation:

You cannot delete a record if either A or B holds values. "Deleting" B while A still has values should result in setting all fields in the B group to empty (and vice versa).

Hmm, yeah I don't know - from my interpretation, this is what happens in a 2 portal example (where Portal 1 is the source and Portal 2 is for lack of better words the target)

Source (RecordId,Value)

================

1,"A"

2,"B"

3,"C"

Target (RecordId,TargetValue)

================

1,"A"

2,"B"

If record 2 (with a field value of : is then deleted from Portal 1, Portal 2 would then take the value of the second indexed record (rather than holding a null value) i.e. it would instead look like this:

Target (RecordId,TargetValue)

================

1,"A"

2,"C"