If and count calculation

[BillAtkin]

I am trying to get a filed to display the last admission dates and I get nada. What I have for the field calc. is :
field= If ( (Count ( Admission date))<2; ""; GetNthRecord ( Admission date; (Count ( Admission date))-1) )
I thought this would go: If there is only one record return nothing if more than one go to the end of the repeating field Admission date and get the second from last. I am desperatly confused, please help.

 

[JeanW]

Your problem seems to come from the repeating field.

You should set up your app relational.

If you still want to follow the repeating road, make sure you find the last entered value from the repeating field (could be by script).
This of cause if there are no blanks in the repeaters.

Your GetNthRecord ( Admission date; (Count ( Admission date))-1) )
will not give you the correct value.

The syntax :
GetNthRecord(yourTableName::yourRepeatingField[numberOfrepetition];2) will return the contents of the second repetition of yourRepeatingField for the second record in the yourTableName table.

This is just one of the problems.
You should setup your file as relational to avoid this problem and many others in the future.

 

[BillAtkin]

Thank you, I am a novice and didn't understand most of that. I am a newbie to databases and am having trouble , I want to set up a relationship between two tables such that an entery in the parent table creates a new record of Admission date and have the last 4 old records be displayed in various field in the parent. I started with a repeating field and quickly realized that was not the answer. I have tried reading about the scripting neccesary to do so and am lost.

 

[JeanW]

Then we need a little more information about what you really want to accomplish.
What data do you want to keep.

You want a relationship between two tables.
Which data do you want to keep in those two tables ?

You want the last 4 old records....from which table ?
The same table or the related table ?

To be able to help you we needto know which version of FileMaker you use, some functions are not available in all versions.

HTH

 

[BillAtkin]

I created a table: Admission Date, I want to keep records of the admission dates, i.e. create a new record using the ID number from the parent so I can display the last 4 admission dates. I set them in a single record portals only sorted decending order in different places. Unfortunatly if I set the relationship between the ID numbers it does not create new records even though I selected that option in the relationship. Do I need to set up a relationship between the admission dates and somehow populate the ID number in the record? I cannot seem to get that to work.

 

[JeanW]

A table for admission dates, good I understand that.

What is your parent ? An other table ?

Which data do you have more besides the date ?

Whatdo you want to show in your portal ?

 

[BillAtkin]

I have a table, 'main' that has most of the entered data (the parent), I have two other tables one for admission info , one for departure info. These will have multiple records, i.e. a client may come in and out of the shelter several times.
I am struggling with the relationship, how do I get the admission dates table; it has a a relationship with main (an =) the client I.D. # (checked allow to create new record) to the main table , to create a new record for each admission while still referencing the guest id#?
The Idea with the portals was to show, at the bottom of the main layout, the last four (portals were to show the 1st, 2nd,etc. records from the child tables; admission dates; from the departure table; departure reasons, interviewer, provider number. I cannot get it to create a new record for each time the admisson date in the main is changed.
I tried a table occurance, also allowing the creation of a new record, tying admission date with a new field in the 'main' ; admission date. Do I need to script something?

 

[BillAtkin]

I figured it out; just have the portal show the empty(waiting for input field, and when you enter it it creates a new record. Now my question is how can I have 'Main' require this field? i.e. you cannot go to another record or layout/table without entering this field.