Hello all,
I have a portal that displays contracts for clients. I figured out how to sort it so the newest contract would be on top and all the rest flow down in descending order. The problem I have, is I want the ability to add a new contract at the top instead of scrolling all the way to the bottom. An empty row there would be the best.
Note: The way I got this to sort descending, is I created a ContractID serial number. Filled in the serial numbers, then sorted descending in Portal Setup.
There is a technique for achieving this which I learned some time ago from someone on this forum (can't remember who, sorry, otherwise I could acknowledge them), but it goes like this:
1. Set the existing portal relationship to NOT allow creation of records. Sort this portal with most recent records at top.
2. Make a duplicate of the relationship to ALLOW creation of records.
3. Duplicate your existing portal, make it just a single row, and switch it to the new relationship, the one that allows creation of records.
4. Place this portal directly above the main portal on your layout, so it almost appears to be part of the main portal
5. Filter this portal using IsEmpty ( <portalTO::primaryKey> ) so that it contains only a single blank row. To create a record, just enter the data; as soon as you click outside the record the filter will remove this record and it will appear as the top row of the portal below, with a new blank row in the data entry portal.
The two screenshots below show this in Layout and Browse modes deployed in a timesheet setup.
Nice technique!
Hi John,
Sounds like you would be helped by my "Reverse presentation order" request.
Best regards Magnus Fransson.
Thanks, Keywords. This makes sense. I've got everything in place, but can't get the IsEmpty() to work so that I have a blank line. It's putting the same record as the first line of the "Don't allow creation of records portal" which is just below it.
Thanks,
-John
I usually put a separate portal above with one line, for that purpose.
It could be for the 'potential' record available normally at bottom when create record is enabled for the TOC, or coupled with Add button.
Set filter to display ONLY the potential with not IsValid(Time::_KPid).
I also have variants where it displays the most recently edited or created.
As long as I'm mentioning single row portals' use them for filtering a summary from the child record below a filtered portal.
The key to the top portal filter is to filter out any record that contains data, leaving only the blank fields for a "potential" record (as expressed by Karen—and yes, Karen, "I usually put a separate portal above with one line, for that purpose." is exactly the method I described).
The filter I use is as described in point 5 of my post: "Filter this portal using IsEmpty ( <portalTO::primaryKey> ) so that it contains only a single blank row." (NOTE: you could also use foreign key as your filter—it needs to be a field from the portal TO that has a value auto-entered when a record is created.) The effect of this filter is to remove from the portal any actual record, leaving only the "potential"record.
If your setup isn't working, chances are you are not using the right field—coming from the right underlying table but the wrong TO, for example.
Yep. I wrote my similar response because the topic is still marked unanswered.
Hi Keywords and Karen,
I'm sooooo close! I think. Keywords, I followed your instructions. What I have looks right, but it's not working so I'm missing something. You say filter on PortalTO::primaryKey. Here's my filter:
Tracker 2 is the second instance of the relationship, with Allow Creation of Records checked. TrackerID is a number field that is autoserialized.
Seems like this should work, but it's not. I get the same record as the first row of my "Don't allow creation of new records" Portal. Is Tracker 2::TrackerID not a valid PortalTO::primarykey? (I'm not exactly sure what a PortalTO is.)
Thanks much! -John
Not sure I follow, but make sure the field for isempty can NEVER be empty.
I made a demo where the top portal fails because of that. Isempty or not isvalid work on the other two.
Never mind the other TOG in the file.
Yeah, here's my setup of TrackerID.
BTW, what's TOG?
Sorry! Table Occurrence Group (but TOC for Table Occurrence) :P
Alright, I'll read up on TOC and TOG.
I'm still fuzzy on why my filter isn't working.
I'm filtering the portal, saying, only show records in which Tracker 2::TrackerID is Empty. Yet, the portal is still displaying records that clearly have a value in TrackerID (See pic from few posts ago).
It appears that the problem isn't with the Field, but with the Filter. Do I have the filter (shown above) formatted correctly?
Can you show a screenshot of the relational diagram? Thanks.
Good morning keywords,
I hope your day is going well. I have a question regarding your suggested solution, which is great by the way. It's my understanding that FileMaker transfers all related records to a given portal on a layout. If you configure a second relationship to allow creation of new records in the second portal, does that double the amount of related records transferred to the host? I'm just curious... Thanks and have a great day!
God bless,
Bill
I don't see anything wrong in the images you sent. I'd have to poke at it to find out what isn't set right. Look at my demo file. Maybe try filter of not isvalid on member ID for the heck of it.
TO means Table Occurrence. Filemaker is a little confusing in this regard. We use the word Table to refer to a division of a database with its set of fields. But, we use the term Table Occurrence to refer to an instance of a Table. (in the attached demo file there are two Tables, called parent and child, but three TOs. Two of the TOs have the same underlying Table (child), so each is looking at the same set of underlying records, but in a slightly different way.
Thus PortalTO simply means portal Table Occurrence, in other words the specific relationship on which the portal is based. For any portal, the fields in the portal need to be from the same TO as the portal itself. (They can also be from another TO related to that portalTO, but that's another story.)
Attached is a demo file that shows the method I originally described. Study it and see if you can spot what you are missing. If you replicate exactly the method shown, yours should work the same way.
I have once seen in Matt Petrowsky that if you filter a portal with "false" it shows only this one empty record that has no data yet. The portal has to be set to allow creation.
This could add to keywords' suggestion.
https://www.filemakermagazine.com/#1546
Here is the link.. he does exactly what you are discussing.. putting exately the same portal on a page with an empty record so you can enter.
Dead right! I just tested "false" as a portal filter on my demo file (see previous post) and that works as well. The logic is not as intuitive, however.
Ok, It's working now.
- I expanded the top portal to 4 rows to see what data was getting displayed and it was showing the same record over and over. For every record in the bottom portal, the first record was duplicated on the top portal.
- I deleted the top portal and recreated it. That worked as it should.
The only thing I can think of, is maybe when I was creating the top portal, I copy/pasted the bottom portal and changed the TO it was pointing to. I'm guessing the fields would still be pointing at the first TO, which was causing the problem.
Huge thanks to Keywords and Karen and everyone else that commented! I learned a lot!
-John
For future reference, there are two causes to what you described:
This is a terrific solution, thanks! I was having trouble until I read all the replies. In the ones posted on October 20th the answer was revealed: make sure that when you place the fields into the one-row portal, you select them from the 'One Row' table occurrence, NOT the 'Many rows for viewing' table occurrence.
Wish I knew how the formula I found over on FileMaker Pro Services works as the filter spec for the One Row table: he used OneRow::RecordID ≠ OneRow::RecordID.
It is actually possible to do this with only one relationship and pair of table occurrences for both portals. The trick is to use a portal filter to omit the "add" row from one portal and to omit all but the add row from the other.
IsEmpty ( portalTable::PrimaryKey )
would include only the "add" row.
Not IsEmpty ( PortalTable::primaryKey )
would include all but the "add" row.
philmodjunk wrote:
Not IsEmpty ( PortalTable::primaryKey )
would include all but the "add" row.
I don't think you can get rid of the "spare" row if the relationship allows creation.
You'd need a portal based on another relationship that does not allow creation, but then of course the filter isn't necessary.
Correct. You don't actually make it disappear, you hide it, making it look like any other empty row and you use Hide Object When on any layout objects in the portal row, not a portal filter on the multiple row portal to get this result.
You can, however, use that portal filter on the single row copy of the portal when using this approach.
Hi,
Vote for: Reverse presentation order, for portals and layouts.
With best regards Magnus Fransson.
There is a technique for achieving this which I learned some time ago from someone on this forum (can't remember who, sorry, otherwise I could acknowledge them), but it goes like this:
1. Set the existing portal relationship to NOT allow creation of records. Sort this portal with most recent records at top.
2. Make a duplicate of the relationship to ALLOW creation of records.
3. Duplicate your existing portal, make it just a single row, and switch it to the new relationship, the one that allows creation of records.
4. Place this portal directly above the main portal on your layout, so it almost appears to be part of the main portal
5. Filter this portal using IsEmpty ( <portalTO::primaryKey> ) so that it contains only a single blank row. To create a record, just enter the data; as soon as you click outside the record the filter will remove this record and it will appear as the top row of the portal below, with a new blank row in the data entry portal.
The two screenshots below show this in Layout and Browse modes deployed in a timesheet setup.