Ben Ball Posted January 8, 2012 Share Posted January 8, 2012 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 Link to comment Share on other sites More sharing options...
stefangs Posted January 8, 2012 Share Posted January 8, 2012 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 Link to comment Share on other sites More sharing options...
comment Posted January 8, 2012 Share Posted January 8, 2012 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. Link to comment Share on other sites More sharing options...
Ben Ball Posted January 9, 2012 Author Share Posted January 9, 2012 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 Link to comment Share on other sites More sharing options...
comment Posted January 9, 2012 Share Posted January 9, 2012 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. I still have the problem in other areas In most cases a workaround can be found. Link to comment Share on other sites More sharing options...
Ben Ball Posted January 9, 2012 Author Share Posted January 9, 2012 OK, Please see the attached file.. I must have deleted it somehow? Is there a work around to this? NN_Copy2.zip Link to comment Share on other sites More sharing options...
comment Posted January 9, 2012 Share Posted January 9, 2012 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. NN_Copy3.zip Link to comment Share on other sites More sharing options...
Ben Ball Posted January 9, 2012 Author Share Posted January 9, 2012 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 NN_Copy4.zip Link to comment Share on other sites More sharing options...
comment Posted January 9, 2012 Share Posted January 9, 2012 (edited) 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? 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. NN_Copy4+.zip Edited January 9, 2012 by comment Link to comment Share on other sites More sharing options...
Ben Ball Posted January 9, 2012 Author Share Posted January 9, 2012 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?? Link to comment Share on other sites More sharing options...
comment Posted January 9, 2012 Share Posted January 9, 2012 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. How is the negative figure returning a red colour? Format > Number… > Format as decimal > Use color Link to comment Share on other sites More sharing options...
Ben Ball Posted January 9, 2012 Author Share Posted January 9, 2012 Yes, understood. The date sort will be fine. Thanks for the time Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4492 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now