Newbies Marc G Posted December 4, 2002 Newbies Share Posted December 4, 2002 Is there a problem with using the "auto-enter from previous record" option on a field in a portal? I have created a db where this option works fine. As the user creates a new record, the vendor field is automatically populated with the vendor from the previous record. However, when I use this db as a child table in a portal, the auto-enter behaves unpredictably. As the user creates a new child record, it populates the field with info from an existing record, but not always the immediate previous record. This produces unpredictable and frustrating results. Is there a sort issue involved (I tried different sort orders as part of defining the relationship between the parent and child db)? Or is it just not possible to use this field option for a portal row? Link to comment Share on other sites More sharing options...
jasonwood Posted December 4, 2002 Share Posted December 4, 2002 Just a thought... Is this database used in a multi-user environment. If two people are adding line items at the same time, the "previous" record might not always be the last record you created. I don't think you want any sorting of the related records. Link to comment Share on other sites More sharing options...
BobWeaver Posted December 4, 2002 Share Posted December 4, 2002 My experience with auto-enter previous in portals is that it's unpredictable. It doesn't necessarily enter the data of the last record displayed in the portal, but the last record in the related database which may not be in the portal of the current record. I made up a sample database that gets around this problem. I will try to find it and post it here. Warning, it's ugly! Link to comment Share on other sites More sharing options...
BobWeaver Posted December 4, 2002 Share Posted December 4, 2002 Okay, here is the sample file. Actually, now that I look at it, the method doesn't seem to be as messy as I thought it was. I threw it together pretty quick a long time ago, and didn't document it too well, so here are some notes to go with it. There are two files, Main and Related. The portal is displayed in main, and the portal data comes from Related. The portal contains a regular field MiscData, and a field "AutoEnterData" that is auto-entered from the previous portal row (if any). Most of the work is done through several self-join type relationships and three utility fields in the related file. These relationships and fields keep track of the last entered data from the related group of records that appear in the portal. Then, the auto-entered field itself is set up as auto-enter calculation. You can look through all the definitions to see how everything works. AutoEnterPrevious.zip Link to comment Share on other sites More sharing options...
Vaughan Posted December 5, 2002 Share Posted December 5, 2002 Warning: I haven't taken the time to actually read your posts above. But I'm gonna take a stab at a suggestion anyway... The Last() function -- though designed for repeating fields -- also has the ability to pull out the values form the last related record. So you could create a script that uses the Last() function to pop the last-entered data into a global field, create a new related record and pop the stored data into the new record. It might be possible to do without the script and use the Last() function in an auto-entered calculation somehow: this will need experimentation. Link to comment Share on other sites More sharing options...
BobWeaver Posted December 5, 2002 Share Posted December 5, 2002 My method doesn't use a script, just some calculated fields and autoenters. But, I wasn't aware that the Last() function would work this way. So, it may very well be possible to simplify my solution. Link to comment Share on other sites More sharing options...
djgogi Posted December 5, 2002 Share Posted December 5, 2002 The only thing that could screw up everything is the sort order when defining relationship, cause in that case at the instance the new record is created in child file, the funcion Last(relName::FieldName) will look at the last record satisfaying the sort order and not the last created record. Dj Link to comment Share on other sites More sharing options...
jasonwood Posted December 5, 2002 Share Posted December 5, 2002 This topic just gave me a great idea! If you number your line items in this way, and sort by that number, you would then be able, through scripts, to insert line items in the middle of the list (by assigning a number like 2.1 to go after the second line item for example). This will be helpful in one of my future databases because I want to be able to enter a "bundle sku" on an invoice. A script would then add all of its components to the invoice immediately after the bundle sku. Yippee! Link to comment Share on other sites More sharing options...
Vaughan Posted December 6, 2002 Share Posted December 6, 2002 "The only thing that could screw up everything is the sort order when defining relationship, cause in that case at the instance the new record is created in child file, the funcion Last(relName::FieldName) will look at the last record satisfaying the sort order and not the last created record." That's easy: create a relationship based on the same keys, but don't specify a sort order. The last record will be the newest. Use this relationship for the auto-enter. Link to comment Share on other sites More sharing options...
djgogi Posted December 6, 2002 Share Posted December 6, 2002 That's easy: create a relationship based on the same keys, but don't specify a sort order. The last record will be the newest. Use this relationship for the auto-enter. That's what I'm usually doing. My standard set up for relating two files: ConstRel const1-->const1 for passing globals ect. CurrentItemRel gfkID-->pkID one to one relationship MainRel pkID-->fkID one to many non sorted with allow creation of related records and cascade delete enabled MainRelationSorted same as above but sorted and with other options disabled MultiKeyRelations (filtering portals, finds ect) Dj Dj Link to comment Share on other sites More sharing options...
BobWeaver Posted December 8, 2002 Share Posted December 8, 2002 Well, this Last() function is going to greatly simplify another little thing that I have in a database that I'm working on. I've never seen anything about using this on a related field before. It's not mentioned in the manual (surprise!). Thanks for pointing it out Vaughan. Link to comment Share on other sites More sharing options...
djgogi Posted December 9, 2002 Share Posted December 9, 2002 Last function Overview Link to comment Share on other sites More sharing options...
Vaughan Posted December 9, 2002 Share Posted December 9, 2002 Dj -- thanks for taking the time to post the definitive information on the Last function! Bob -- what goes around comes around. Earlier today I was able to remove an ugly Case() function that had (English) month names hard-coded into it and replace it with a function whereby the last day of the month is calculated as the day before the first of the next month. Yeah! Bloody clever that, a real head-slapper. Thanks LaRetta for mentioning it in your post! Link to comment Share on other sites More sharing options...
djgogi Posted December 9, 2002 Share Posted December 9, 2002 I like these functions: PrevMonth=MonthName(Date(Month(Status(CurrentDate)),0,Year(Status(CurrentDate)))) LastDatePrevMonth= Date(Month(Status(CurrentDate)),0,Year(Status(CurrentDate))) CurrentMonth= MonthName(Date(Month(Status(CurrentDate))+1,0,Year(Status(CurrentDate)))) LastDateCurrentMonth= Date(Month(Status(CurrentDate))+1,0,Year(Status(CurrentDate))) NextMonth= MonthName(Date(Month(Status(CurrentDate))+1,1,Year(Status(CurrentDate)))) LastDateNextMonth= Date(Month(Status(CurrentDate))+2,0,Year(Status(CurrentDate))) Dj Link to comment Share on other sites More sharing options...
LaRetta Posted December 9, 2002 Share Posted December 9, 2002 Thanks so much ... Well this Forum is truly miraculous!! My studies for tonight were on Date functions ... and I've hit the mother lode today I've now added this tid-bit to my stolen (errr, acquired) knowledge base LaRetta Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7972 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