11 replies to this topic

[Ben Ball]

Hi,

I have a table where I need to view the same table, only each record needs to view the previous record. Apart from the 1st one (obvilously)

How can I do this? I set 2 calc fields. But the relationship model wont let you view records for 2 fields that are based on calculations.

So, how can this be achieved?

For example. I have a unique ID calc field (ID)set to "get (record number)"
I have another field (ID_VIEW) as a calc field set to  ID-1

therefore ID_view will always have the same number as the previous record, except the first record which see nothing.

I can't see a way of having a field, other than a calc field, that will reference the previous record.

Please help.

TIA

[stefangs]

the get (record number) function may give you unexpected results once you start changing the sort order, be sure to check the 'don't store results' box in the calc options.

what you are trying to do should work. i've done similar things. perhaps you're just not displaying the previous record correctly. it should appear in a portal. you can hide all the graphic elements of the portal, since you are only displaying a single record.

hth,
stefan

[comment]

As Stefan hinted, you need a more precise definition of what is "previous record". If you do want it to depend on the current found set and sort order, you can use the GetNthRecord() function to get data from the previous record - even without a relationship.

[Ben Ball]

Thanks for the responses..

Stefan.. I cannot get it work using a relationship portal. Please see attached file. I just get the filed showing "<index" as although the getNth record function solves my problem... I still have the problem in other areas where I cannot use 2 calc fields in the relationship model.. It always give me      "<index".. any comments appreciated.


Comment...

Thanks, I have implemented the GetNtrecord function within the attached file. Which works great.



TIA

[comment]

There is no file attached. In any case, you cannot use an unstored calculation as a matchfield field on the "other" side of the relationship - and Get (RecordID) must be unstored in order to reflect the current found set and sort order.

Quote

I still have the problem in other areas

In most cases a workaround can be found.

[Ben Ball]

OK, Please see the attached file.. I must have deleted it somehow? Is there a work around to this?

Attached Files

•  NN_Copy2.zip   5.53K   6 downloads

[comment]

Your file doesn't tell the entire story. What is the purpose of showing previous balance? In most applications, I think, you'd want this to ignore the current found set/sort order - as shown in the attached.

Attached Files

•  NN_Copy3.zip   5.94K   5 downloads

[Ben Ball]

I have attached the file with what am trying to achieve.

Implementing both solutions.

The left portal shows the solution using the GetNthfunction.
The right Portal shows the solution using the relationship model.

I had to do further work.. adding calc fields to show what I am trying to achieve. The left side is working. The right side needs work. Basically... the right side will only be correct if the serialID is continous... which is the problem I was having.. if records get deleted etc. So, you will see if the seriald's change to 1,2,3 etc in order then it will work.

Basically I was wanting to create a balance sheet to balance accounts using the previous balance from the record before.

I was trying to achieve this whilst being able to "insert" records if mistakes were made by the user... therefore, depending on the sort requirements the system would update the balance automatically. Also, if necessary if a record was missed out. This could be added and repositioned.
I did'nt get that far yet.. But the attachment will give you an idea of what i was trying to achieve.

cheers

Attached Files

•  NN_Copy4.zip   7.48K   5 downloads

[comment]

Quote

But the attachment will give you an idea of what i was trying to achieve.

I am not sure it does. Perhaps it could be much simpler?

Quote

I was trying to achieve this whilst being able to "insert" records if mistakes were made by the user...

That's possible, but records must be in the correct order. Your transactions table should have a Date field, and your relationship should be sorting by that.

Attached Files

•  NN_Copy4+.zip   6.88K   6 downloads

Edited by comment, 09 January 2012 - 07:57 AM.

[Ben Ball]

Yes, thats much simpler.. once I know how..
I did think about sorting by date but then it may get messy when multiples are added on the same day and deleted and swapped etc.
Maybe datestamp, but then I think it can still get messy.

One more thing. How is the negative figure returning a red colour? or is that something filemaker just does automatically? I can never seem to make it do that.. its always black when I create - figures??

[comment]

Quote

I did think about sorting by date but then it may get messy when multiples are added on the same day

Well, you need to sort by something - and if records are not entered in chronological order, then serial ID cannot be it. I am not sure the interim balance during a day is important, but if it is then you also need to know the order of transactions.

Quote

How is the negative figure returning a red colour?

Format > Number… > Format as decimal > Use color

[Ben Ball]

Yes, understood. The date sort will be fine.

Thanks for the time