Add new portal row below selected row

[Shana]

I have searched through a lot of past posts but I have no found the specific answer I am looking for.

I have a database with hundreds of records. Each record has a portal that contains several fields with 1 row of data to hundreds of rows of portal data. There have been several times now when users have entered large ammounts of data but had forgetten a line of information. These lines need to go in a specific order since they log time codes on several tapes for each project our company is working on. The users need to be able to insert a portal row in the exact place they had forgotten teh information (could be the 2nd row could be the 75th). Is there a way to do this?

Thanks

-Shana

[Ugo DI LUCA]

Why not sort your portal by the Time field ?

If this doesn't work, then you could script a sort order and renumber portal rows according to your needs.

Check Dj's sampler for "Renumbering and moving Portals" in the Sample section.

[Shana]

Well I tried to sort the portal by date but I was unable to get this to work. it only sorted the records in the main file and not the ones in the portal.

[Ugo DI LUCA]

Hmm...

It's an option you can check in the Define Relationship Box.

Go see your relationship, check this box and select the field and order you need it to display in the portal.

It won't sort the Main file, just the portal relationship.

HTH

[John Caballero]

Sorting the relationship by Date may or may not work. If you have more than one related record per master record with the same date, you'll need to sort by a second field, mostly likely a Time field.

If it's the case that there are lots of records on the same date it won't be very user friendly to ask the user to "back date" (and possibly "back time") their entry to make it fall in the right sequence. I haven't looked at DJ's sample, but numbering the portal records is what I think would be a better solution - requires a bit more savvy on the developer end, but makes the user experience much less painful.

[Shana]

actually, sorting by date worked!!! thank you so much!

[Shana]

On second thought, this does not work. While it does sort by date, I can't have it do that on EVERY record because some of the entries in the portals do not have dates but need to stay in the position they are at. Therefore, I need to sort only ONE portal in a record or just ADD a ROW like i thought from the beginning...any thoughts?!?!

[Ugo DI LUCA]

You would have issue scripting a renumbering portal row anyway, as I cannot imagine on what parameter you'd be building the custom sort. You may set it manually when needed with Dj's technique, but I guess there is some other logic you might want to explain.

some of the entries in the portals do not have dates but need to stay in the position they are at.

What is the clue here ?

[Shana]

so is there no way to just add a row wherever needed?

[John Caballero]

It's not quite as easy as just adding in a row like you would in Excel. You CAN add a row in a portal view, but you need to have a field to sort by or the records in the portal (you set define this "sort by" field in the relationship definition). Ideally each record in your related file would have a field that was an auto-entered sequential number. For your purposes I would recommend that they auto-increment by 10s, so that if you needed to insert a record between 70 and 80, for instance, you would simply create the new record like you normally do but then overwrite it's sequence ID with 75.

[jondolar]

Hi Shana

It is entirely possible to insert a portal row wherever you want. Here how I did it. First you should have a little button on say the extreme left of your portal rows that trigger a script. The script goes roughly like that. Assume that you have 3 related fields per row, you have to create 3 global temporary field in the child file. The script will essentially go to the row before the last portal row (the empty one)and through a loop, moves down the content of each field.

Set a global field gcounter Status(CurrentPortalRow)

Goto last portal row

Set a global field glastportalrow Status(CurrentPortalRow)

if gcounter >= glastportalrow

exit record request

exit script

end if

goto portal row previous (Select entire content)

loop

set field gtemp1 field1

set field gtemp2 field2

set field gtemp3 field3

goto portal row next (Select entire content)

set field field1 gtemp1

set field field2 gtemp2

set field field3 gtemp3

goto portal row next (Select entire content)

exit loop if status(CurrentPortalRow)= gcounter

goto portal row previous (Select entire content)

end loop

clear field1

clear field2

clear field3

That's it

Not very elegant but it works.

Good luck

Serge

[Ugo DI LUCA]

Shana,

I said "What is the Clue here ?". This was suppose to mean "What is the Logic ?".

Serge, it surely is possible. I'm rather concerned about how dynamic (or confusing) it may be to have it done, using your method or an adapation of the previously mentionned Dj's sampler.

I'm quite sure there is some logical structure Shana isn't sharing, that will allow to have it sorted wherever the date is stamped or not.

[jondolar]

Ugo

This has been working for 5 years in an order taking solution for an audio-visual material renting outfit.

Serge

[Ugo DI LUCA]

Serge,

Again I have no doubt it works. I'm also using it in "Preferences" file and to convert a given related records to a Default value for lookups....

So when there's no logical sort and I need to build some custom ones.

I don't know for what purpose you're using this method, but back to Shana's case, and in extension for any solution where there's a logical sorting (Time/Hour are logical parameters), I'd look for another solution...

Besides, Shana said

"There have been several times now when users have entered large ammounts of data but had forgetten a line of information. "

I can't imagine a solution where I'd ask the user to look in a portal with a few hundreds (even twenty actually) records, identify the correct position, and insert a new row. This is pure waste of time, just what I'm asked to avoid when developing a db system.

There must be another way following the dates/hours logic, may be with some calculations behind. Just my "narrow minded" brain for sure...

[Shana]

Well, the logic is simple. I work at a post production house where we have libraries of tapes for each project we work on. We keep index cards with the time codes of every item on every time. Sometimes there is only one item on a video tape and sometimes there are a lot of items depending various factors.

I am in the process of converting all the hand written cards that go back several years to the database i wrote. We have already converted over 500 cards and when we checked back it has come to our attention that lines were neglected on the cards with lots of information just due to human error. A lot of times however we have come across people who are too busy to write down the data immeditaly after laying off to tape....several days later we will need to go back and enter the data in the correct spot because usually more stuff is layed off after the data. When it is a handwritten index card, it is not difficult to squeeze in a line written small but this is not the case with file maker. It is very important that everything is int he correct order. Not every line of information has a date or a time or even a timecode because sometimes it is a group of data with one large timecode and we put notes on the following lines.

[BobWeaver]

All new records are created at the very end of the file and they never physically move. In database terms, by not having some information in a record that explicitly tells it where the record belongs in the sort order, you are really saying that order is unimportant. Obviously, that isn't the case in your situation. I think that is the point Ugo was making.

So, if I understand correctly, the items that don't have a timestamp really correspond to the last timestamp above where the item should appear. In that case all of these items should be given the same timestamp. Then you can sort the portal by timestamp and all the items will automatically group together. You can also have a subitem number field that sorts the individual items within a timestamp group.

On your previous paper system, this timestamp ordering was implied. When someone looked at an item without a timestamp they automatically referred to the last one above without even thinking about it. In a database system, you can't skip details like this.

[Himitsu]

this is just a hunch, but I think shana is using or used a a template that was for selling an item. Now shana is using the record for the cards and the portal for the data on each part. Am I wrong? I did this one time too. If I am wrong then sorry. But I think maybe Shana should explain how he/she is laying out this project to see how it all fits. I think Shana is missing something, ie, a record for each data line that has an id# that corresponds to the card record then a portal that relates all the id# and data files to that card... get where I am going? Someone is talking about a VW Bug and the other is probably talking about a 300ZX. Just my two and a half YEN.

[zincb]

A simple solution is to add a numeric field to the child file, called SORT.

Create a script to find the related rows, sort them by this SORT field, and then loop through the sorted list and, starting with 1, incrementing SORT in each row of the found set, by 1.

In the parent file, modify your relationship to sort portals by the SORT field.

Now, if you need to add a row between portal rows 74 and 75, add a row to the portal and enter 74.5 in the SORT field. Then run your script to sort & renumber the SORT field.

[Shana]

I created the database from scratch. I am a computer programmer and this is my second filemaker database that my company had me learn so that I could make databases for them. As I said before not all cards have time codes or dates and this is mostly because they correspond to data above however this is not always true. several times artists will forget to write down the time code and then it is just left blank. I am not sure how a sort could possibly work because I need to add information days or even several years later. The system is already in place and the reason I had posted the question was because several artists have been coming up to me saying they forgot to input something a while ago and now they need to but there is no way to add cards (also i forgot some lines of older cards when I was originally entering them in).

I am going to try Zincb's idea of the numberic field sort. thanks.

[Shana]

nevermind that isn't going to work. my boss doesn't like the idea of numbering every row.

[zincb]

So use your boss's idea, instead.

Ooooh! I get it! Your bosss doesn't HAVE an idea!

Sounds like my pointy-haired boss, too.

[jondolar]

Hi Shana

I think I gave you a simple and viable solution to your problem in an earlier post. Would you care to comment, to ask questions?

Regards

Serge

Just trying to help

[Ugo DI LUCA]

But why wouldn't it be possible to add cards ?

[zincb]

Would it be possible to duplicate the portal row before the one where you want to add a row, and add 1 to its Time value?

Then, in your relationship, you could sort on Date and Time, as was previously suggested.

[Shana]

i didnt know oyu can duplicate rows!!!!

[BobWeaver]

Duplicating is essentially the same as creating a new record and then copying the data over from the original record. The duplicated record still physically goes at the end of the file. Where it is displayed in the portal depends on how you are sorting it. And if you are sorting, then you have to have a field to sort on, either a time/date stamp, a serial number or whatever. You don't have to manually enter the numbers and the user never has to see them.

But, then again, Serge's method of physically moving data from record to record will certainly get the job done.