Can List View auto-update like a Portal?

[Devyn]

I'm working on an Invoicing solution and I'm trying to decide between displaying Invoice Items (a table of labor and materials consolidated for reporting) in a portal or in a list view layout.

Items are added to an Invoice from a floating menu of billable items. I need to Display the Items that have been added to the Invoice.

I would like to retain the easy searching and sorting, and potential dual use of the List View layout as a Reporting layout, but I haven't come across any elegant solutions for auto-updating the displayed found set when Invoice Items are added.

In short, I'd like to get a list view layout to act like a Portal showing only related records to the invoice.

Does anyone have any tricks up their sleeve?

Thanks for reading.

[Fitch]

I presume you click a button in your "floating menu" to add an invoice item. That same script can then perform a Find in the target window. Is that what you had in mind?

[Devyn]

Well that's what I've settled on so far. It works pretty well.

What I'm worried about is the users' ability to search the Display Window (which I want them to have) turning up Invoice Items that don't belong to the invoice being worked on. Its not the end of the world but it could be confusing.

I just thought I'd see if there is some magical way to restrict a List View layout's perspective via a relationship.

[Søren Dyhr]

I just thought I'd see if there is some magical way to restrict a List View layout's perspective via a relationship.

Do you mean dwindling here? This thread might give you some ideas:

http://www.fmforums.com/forum/showtopic.php?tid/192169/fromsearch/1/hl/dwindle/tp/0/all/1/

--sd

[Devyn]

There's some cool stuff in that thread.

When using a portal to display related data, you only see that related data. There is never the chance of seeing non-related data. In my scenario, adding an Invoice Item, the record would immediately appear in the portal. Any filtering I apply to the portal will dwindle only the records displayed (as you said).

I would use a portal if it could be searched with find mode and sorted by the user. As it is, the searching and sorting are too important to give up. Hence I'm fishing for a way to stop my List View layout from being able to show any other Invoice Items besides those related to the current Invoice.

The user must be able to perform finds on the Displayed Invoice Items (which opens up the full set of records by default) without finding anything not related to the Invoice they're working on. Limiting them to a constrain find button and a hijacked show all records button would work, but I know no way of disabling the Enter key to submit a find request.

Another way to say it might be, I want all the functionality of a portal with the searching and sorting abilities of List View. Superportal? Hybrid List View? I don't know.

I suspect it isn't something that can be realized elegantly but I'm no pro like you guys. So I asked

Thank you so much for your replies.

[Søren Dyhr]

Could I ask you to keep an eye on this thread, because I would think I could nail it with some further intergation. Only thing is that it's more likely to be during next week, since the next 3-4 days is a tightly packed schedule.

--sd

[Devyn]

Ya, I'm not in a hurry. I've got the thing working using a script updated found set every time an item is added.

The user will just have to deal with seeing the occasional stray item that shouldn't be there when they search.

Thanks again, I'll keep an eye out.

[Søren Dyhr]

What would prevent you from folding the listed view in the new window into a self-joined dwindled portal??

--sd

[Devyn]

[invoice] ---< [invoice Items]

The menu is based on Invoice, the list view is currently based on Invoice Items right. If I end up switching to a portal, I'll simply put a portal of Invoice Items on the menu layout. This is what I had first, but the client asked to be able to search the Invoice (I guess they can get quite long...) and I don't know of any way to search a portal.

Now, I've put in a lot of hours on this thing so far, so I might sound like I know what's going on but I'm very new at this so there may just be a simple setting to search a portal that I've just never used before.

Great advice so far btw. I've found a lot of helpful posts here.

[Fenton]

Does the client really need to "search" the portal, like a (loose) Find, or would he be satisfied to "filter" the portal, by known criteria? There is a big difference between a Find and a filter. A Find requires a Find (duh), whereas a filter can be relational.

A filter works by adding a criteria to the originating side of the portal's relationship, so that the relationship is filtered, hence the portal. For example; show only labor items, or show only material items. This could be extended, via 2 filters, to show only a specified Type of labor or material items. There are methods to let you switch back to seeing all the items. The relationship would also include the Invoice ID, so the above would always be within that Invoice.

If an actual Find is necessary, it can be done, though you may want to use a 2nd portal for it. You can do this within a Tab object, in order to use the same layout space. It would need to be scripted, though the script could be run via a (free) event trigger plug-in.

You would go to the line items table, do the Find, including the Invoice ID, to keep the results within that Invoice. Then capture the unique line item IDs of the found set, via Copy All Records, a Loop or a Custom Function, setting them into a text field in the Invoice table. Then use a relationship based on that field to the line item ID to show a portal of the found set in line items.

Both of the above methods require use of global fields to enter the criteria for the filter or the Find, and a modification of the originating key, to show the results.

[Søren Dyhr]

Perhaps is it the WYSIWYG that bothers here, you do apparently wish to compile an invoice, but it's the plucking of items, perhaps even with an indication of availability - which is likely to be an unstored value, since it's a value pulled over a relation, which unfortunately rules out drop down or anything else value list'ish.

This brings forward another approach, why not have a perhaps tabbed system of sub-selections of the entire list of items, where the one who takes the orders pretty much like a form next to each item makes note of quantity desired.

Take a look at this utterly clever template of Bruce's:

http://www.fmforums.com/forum/showpost.php?post/149069/

...which very crafty makes use of this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

Via a global primary key!

Alright it's indeed a departure from entering data into itemlines in a portal, like a fallback on old fasion ordering forms.

--sd

[Devyn]

That's a cool example soren. But (there's always a but eh) I'm not having trouble adding items to the Invoice, its searching within items already added to perform editing and override tasks.

The company I'm working for is in Landscaping, so the numbers can be a little fuzzy. Tweaking invoices here and there to meet quotes is a regular occurence. They've asked to search these invoices for this reason:

Person doing invoicing needs to make adjustments to the bottom line and remembers some detail of the an item suitable for alteration.

To find the item (in a list of potentially hundreds of individual labour and materials records) they want to type that detail into the relevant find field with all the nice find tools that filemaker provides. Maybe they remember the original price was between 50 and 100 dollars, or the employee who entered the Production Sheet said something memorable in the work description (all of which appear in the invoice).

I'd rather the user not have to go out into the full set of Invoice Items (past and present) when they are looking for one particular Item on the current invoice they're editing. It should be a quick, Find, type memorable detail, enter, modify the item(s).

I suppose on entering find mode one could fill the invoice foreign key field for the user and then disallow them from editing it in find mode. Is that reliable?

[Devyn]

Fenton, I presume the user would type their find criteria into some global fields on the "Display Invoice Items" layout, click find, then the script would hop to a list view and carry out the find with the global values.

Wouldn't you have to perform a bunch of special handling for multiple find requests and special find characters in order to make use of these vital features? Could it even be done?

[Fenton]

Well, the more flexibility they want, the more difficult it becomes to just "enter data in a few global fields, then hit 'Search'." Personally when it becomes too difficult, I'm tempted to tell them, "Why don't you just go to the line items table and learn how to use FileMaker Finds?" I often do say that. It depends on the complexity they want, the amount of interface control needed, and the person's skill, as to whether they will accept that. It's like the old story of giving a man a fish, or teaching him to fish. Unfortunately most will respond, "Just give me the darn fish!"

In which case I would say, "OK, but you will not get the widest variety of fish that way," as well as, "What exactly kind of fish do you want?"

There have been jobs however with highly controlled interfaces, results in portals, multiple tables involved, etc., when a scripted search was the only option.

There are several possibilities. When you talk about "multiple find requests," I start thinking of taking them to a dedicated Find layout (of line items) where they can be in Find mode, then continue via a script. That way you can get the flexibility of Find mode, while still controlling the result (filtering to only 1 Invoice). You just have to make sure they can't accidentally get out of Find mode and start entering data.

Or you could have a dedicated table, with the fields needed. Delete All Records, enter data in regular fields, in Browse mode, take those values to reset up the Find in the real table. But that's kind of duplicating what Find mode does, with extra work.

Or you can use global fields, with multiple repetitions. You can separate the repetitions so they look like separate requests. You can also loop through multi-line fields, to translate them into multiple requests. Omits can be done, as a Boolean checkbox field, but they should be instructed to put them at the end. In other words, you take all these values in global fields, then translate them into the proper structure and values in a script.

That's the method I've used when I had to. It has the advantage and safety of being in Browse mode and never exposing the regular fields. But I'd want some reasonable limit of fields and requests required.

So, all and all, I don't know the perfect method for all situations; I pretty much only know what I might pick for a given situations, and that's only a "might." But if you decide on a method, and it makes sense, we'll try to help -]

[Søren Dyhr]

"Why don't you just go to the line items table and learn how to use FileMaker Finds?"

This is an excellent point Fenton raise here, filemaker is first of all a groupware, and not something which should be utilized too heavily as a mean to produce foilwrapped solutions, because other tools does it so much better, provided the right skills set is present between the ears of the developer!

It can't be stressed too much ... do not attempt to pull filemaker out of filemaker's own realm - other than just for fun, a realm which Ernest Koe here eloquently describes:

http://www.proofgroup.com/articles/2006/jun/filemakery_part_i

I can however easily imagine how tiresome it is for the user to flip back and forth between the items list which could be presumed near endless in detail. Here could a recursive structure perhaps help in another way, namely by grouping items with a certain coherence into a single itemID which then after arriving into the invoice/quote gets trimmed in quantities and perhaps even deletion of certain items not really necessary. Previous invoices contents could also be grouped into a single itemID. There are a few different ways to accomplish this:

1) the genuine recursive many many structure , or...

2) http://www.dwdataconcepts.com/dl/tw/compinv2.ZIP

--sd

[Devyn]

Wow, that article was brilliant. Thanks for that.

I really appreciate your help guys. Thanks. I think I'll be substituting some user training for design in this case. :)

[Fenton]

I really don't think that your problem is so much that you need a really "high-end" relational structure, it's just that what you want to do, combine a controlled portal view with "flexible and unlimited" Finds is both awkward and difficult.

You really need to find out what the client "needs." People are often vague about this, "Just let me find in the portal," whereas the truth is: 1. They don't understand that a Find in a portal finds Parent records, not child records per se, and 2. There's really limited criteria that they need to find, which often can be more easily and accurately produced by filtering a portal relationally.

[Søren Dyhr]

I think I'll be substituting some user training for design in this case

It is what I would call a very sensible discovery, and while you're at it - say you have a category field, then teach the user to put the cursor in that field and use the shortcut to find similars ....

In my OS is it done with the right mouse button, where a "dish" in the menu is "Find Matching Records" ... no need to turn the user into find-mode, enter the date etc.

--sd

[Devyn]

I didn't know about that context menu search. That's neat, thanks.

So here's the final solution (until the client asks for changes ugh..)

User creates an Invoice, and enters the Invoice Builder. Only one invoice can be displayed in the builder, necessarily no provisions for hopping between invoices from there.

Builder windows. Menu Window. Display Window.

A menu window displays billable Projects, their associated billable work orders, and THEIR associated billable Labour and Materials. The menu is very slick, select multiple items, filter by drop downs view related records full details, total number of billable "children" and their dollar totals as well.

Any Item or items selected and added will add all the Labour and Materials through all relevant relationships to the invoice. So you can select a project and get all its labour and materials added at once. Or select a work order and get just its labour and materials, or select individual labour and materials. All of which you can select multiple and add them all at once.

The Invoice in the Menu window shows its related labour and material items in the Display Window (The list view I went with)

Each item added refreshes the displays found set transparently. As does each remove item. Items may be freely removed from the invoice and reappear in the menu as billable items.

User may override any value on the invoice (with the proper privileges). Overriden values are identified clearly. User may fabricate invoice items as well in the event a billable service didn't follow the normal Work Order, Production Sheet route into invoicing.

User may search the invoice freely with full find mode functionality. When they enter find mode the script grabs the invoice primary key from the menu window and inserts it transparently. The user is then not allowed to change it.

What do you think??

I wanted to post it, but its very intertwined with the rest of the solution and I'm not really sure of the precedents of posting a gigantic finished solution for all to see...