Scripted tagging a big "no-no"???

[Simon K]

I recently responded to a post by Bluearrow

Topic #191849 re "Last available related record in a one to many relationship"

I am soon to come into a very similar situation in my own development requirements:

That is to say:

I have the following relationships:

One client may have many projects

One project may have many cost centres

One cost centre may have many tasks

One task may have many timesheet entries

Periodically, I want to raise invoices to my clients for the time spent on their affairs (potentially spanning multiple projects etc).

The invoice will normally be for the total time spent on their affairs based on work we have completed.

So our suggestion is that users will flag tasks as complete (simple check box on a task layout).

The setting of the flag will indicate that the task is billable

During the invoicing process I want to choose which billable timesheets (this by reference to the task status field) to include on the invoice ie some time may not be billed if it is considered non chargeable/irrecoverable at the point of invoicing.

Once all tasks have been invoiced (or written off) the cost centre should no longer show for billing

Once all cost centres have been invoiced the project should no longer show for invoicing for a client.

I prepared a prototype (attached) (based on response to Bluearrow topic) but it has been indicated that the "scripted tagging" approach is not robust and certainly not the right way to go.

I would very much welcome comments on how to achieve objectives and the correct methodology to adopt.

Certainly one of my (general) challenges seems to be the proliferation of table occurrences.

Many thanks

Simon

ProjLinesInv.zip

Edited December 11, 2007 by Guest

[Søren Dyhr]

Give yourself some time to digest this method:

http://fmforums.com/forum/showpost.php?post/266487/

At first sight doesn't it seem to even remotely similar to your problem, but notice what the approach does ... it moves the processing to the other side of the relation...

--sd

[Simon K]

Thanks Søren,

I will indeed give this some time.

At first sight though I think this where I fall down as a programmer - as a template solution it seems too removed from my scenario and too abstract.

In particular I don't know what tables/records I should be creating in relation to the situation outlined...

Still, I will work through the demo a little later today to see if I can make some self learning happen and get that personal breakthrough that leads to enlightenment!

Rgds

S

[David Jondreau]

Sounds like you've got a big project and this is box I'm typing in is pretty small.

You've got tables Clients, Projects, Cost Centers, Tasks, Timesheets and Invoices.

In addition to all the Primary key and foreign key fields you obviously need to relate the tables to each other, I'd say you need at least the Client ID in all the tables, if not all the parent primary keys. (so Timesheet entries would have the fields, ClientID, ProjectID, CostCenterID, and TaskID). It may be a little overkill, but you might find those fields useful later on. Oh yeah, and an Invoice ID field.

I think you need two number fields in the timesheets table, one to indicate if a timesheet is billable, another to indicate if it has been billed. You can go down your portal (or filter it so billed timesheets don't show) and check off what sheets are billable. Then run a script that will create an invoice, find all the timesheets that are marked Billable but not Billed and set their InvoiceID to the new InvoiceID

[Simon K]

Thanks for the response DJ

Yes the project is big and probably getting bigger as I type!

I think I totally understand the fks in the timesheet table you are suggesting - no worries there.

On to my troubled areas...(next bit as I think),

The two number fields that you suggest... one that says whether its billable and the one that says whether it has been billed...

If the list of timesheets (for a task/CC/Proj)is in a portal and you go down the list and flag (by ticking a check box?) whether its billable - am I correct in assuming it will store a 1 or a 0 in as a result?

I understand a script that updates the timesheet lines with the invoice_id. OK (I can also understand setting up a new table for invoice lines with denormalised data in instead)

But the other number field - the one that says whether its been billed - is that an unstored calc based on the presence, or not, of an invoice_id in the fk_invoice_id field? ie still storing a 1 or a 0?

And following on to the real crux of the post, how should that be then used in working out whether the marco entity in this case a project has been fully billed?

ie given that the input I have received is pretty much universally against scripting such a flag?

I am still struggling to understand how Søren/Fenton's demo works or more relevantly how I can apply it to this situation.

TIA

Simon

[Søren Dyhr]

Basicly do you need an extra TO of the same table, and if a condition is met in the child-table, will the ID of the record be used as primary key for the selfjoin via a calcfield locking to the ID in the selfjoin, then by displaying the selfjoined table's data instead of the most obvious, will the filtering happen!

In essence the same as you would have accomplished by scripting a replace taking each related records values in consideration, where you then provide each record wich matches the condition with a new stored value a "tag"

The attached template is another variation over this approach, which by the way right out of the box could manage a Bed & Breakfast, if you or others should wish so.

The template is attempted to deal with some kind of concurrent bookings made by several persons picking the same resources, hence the snapshot'ish way available premises are shown for only 3 seconds, in which a resource should be picked - hopefully won't two concurrent bookers try to book in total synchrony ... Better measures can be instated to prevent these issues however!

--sd

Leases.zip

[bluearrow]

The method and file described here looks very similar to one I use (and favor very much) for highlighting a portal record.

I maybe missing something, but I am still left with one question that I guess Simon also might be needed a reply for:

1.- How do you flag the parent parent table as "Not available" when all child records are "not available"?

I guess you still need some kind of flag here if you want to allow your users to search and find available and not available parent records.

The method is fine for filtering and showing in a portal, but: what if you do not want to use portals? how about finding records that meets the criteria using a checkbox or dropdown list (available/not available)? I do not see any other way but to create an additional calculated field. If so, then we might be missing the main premise: not to flag the records.

Edited December 14, 2007 by Guest

[comment]

A calculation field is not the same as marking records. For example, an unstored calculation =

Count ( Child::ParentID ) = Sum ( Child::Status )

returns true when all parent's children have a true (1) status. Unlike marking, this requires no maintenance by a script. The challenge here is that being unstored, the calculation field cannot be used directly in a relationship that filters parents by their children aggregate status.

[Søren Dyhr]

but: what if you do not want to use portals?

Make the selection by GTRR(SO) - via the multilinekey!

http://www.filemaker.com/help/Script-Steps19.html

...or do you mean how to pick one of the premises without a portal??

--sd

[Simon K]

Sorry for delay in replying - been out of action for a couple of days.

Anyway (re Michaels last post),

I sort of see what you are driving at but am having problems translating that to the file I first posted .

For my own learning I feel I have to successfully rewrite the file to "no script tagging approach"

I would be grateful for confirmation of the following:

1) Given that I believe I will have to have a separate table for invoice_lines...

then the relationships for Invoice_hdr to Invoice_lines and from invoice lines to project info on invoice and line info on invoice are correct.

Actually I seem to have lost the plan after that point so some slightly more explicit guidance would be really welcome (atm cant even work out where Michaels calc is supposed to go)

Not being lazy - I cant understand the translation of Sorens point

"Basicly do you need an extra TO of the same table, and if a condition is met in the child-table, will the ID of the record be used as primary key for the selfjoin via a calcfield locking to the ID in the selfjoin, then by displaying the selfjoined table's data instead of the most obvious, will the filtering happen!"

and already mystified at Michaels comment that you cant use the calc field in a relationship

I am willing to go try some more but I could do with another push!

Many thanks

[comment]

You CAN use an unstored calc in a relationship - just not on the "many" side.

Here's a simple demo showing how to filter a relationship by an unstored calculation field. You'll find more of these if you search for "Ugo's method".

Untagged.fp7.zip

[Søren Dyhr]

Instead of my speaking of the "most obvious" have Michael shown both portals ... much better for illustrating the approach indeed!

If it still doesn't make sense, take a look at the two portals in layoutmode ... tunneled values from more than one relational hop away.

--sd

[Simon K]

Hi Michael,

I have now had a go at rewriting the orignial file using "no scripts" etc. File attached.

I think I understand the basis for the calc in the parent table based on the child table which is then used for a further TO.

I did have some problems with how to show the open lines still to invoice.

I would be very grateful if you could give it a quick "once over" just to make sure I have understood the principals.

(Incidentally, denormalising the "lines info" into the invoice lines table is deliberate)

Many thanks

Simon

ProjLinesInvUgo.zip

Edited January 11, 2008 by Guest

[comment]

I am sorry, but I just cannot find my way in your file. It's probably my limitation rather than any flaw with the file itself, but my brain cannot get past your naming conventions and the way you have arranged your graph.

[Simon K]

Hi Michael,

I really appreciated you taking the time to look.

I did have problems "changing" the first file.

As you may remember, I do not have a huge history with Filemaker and would appreciate any guidance you can give on what should have been done on naming convention and also laying out the graph to make this example more "normal" to somebody with your exp.

My point being that I have copied the conventions I have seen in my inherited app and therefore anything you can add or encourage me to change would obviously get me more on the right path to better dev.

[in this case the demo was an invoicing screen where the user has to chose Project "lines/costs" to bill to customers with the requirement that only those projects with un-invoiced lines would show etc.]

Either way, thanks for the input.

Simon

[comment]

There are no rules AFAIK - I can only tell you some of my personal preferences:

• Tables are named by the entities they represent (e.g. Invoices, LineItems, Projects, Tasks, etc.). I try not to use the name of one table as part of another's, since that implies a join table (e.g. ProjectLines implies a join table between Project and Lines).

• Each table has a field named after the table with an "ID" suffix as its primary key. Therefore, a field named "ProjectID" is the primary key if it is in the Projects table - anywhere else it is the foreign key to the Projects table.

• Fields and tables are named in UpperCamelCase. I try to avoid using underscores. Calculation fields are prefixed by "c", globals by "g" and summary fields by "s". Repeating fields are suffixed by "R".

• The top row of the graph represents the 'core' relationships of the solution. For example, in a typical invoicing solution the top row would show:

Customers --< Invoices --< LineItems >-- Products

• Each base table has a layout that belongs to its 'core' TO, and is named the same. I try to avoid the anchor/buoy method if at all possible.

• All TO's of the same base table have the same color.

That's all I can think of at the moment. I am sure there are some more - just as I am sure that I break these whenever it suits me.

[Simon K]

Thanks,

I have redrafted file using those guidelines (I hope you don't mind me continuing) totally understand that these rules do need to be flexible!

File attached

Is this easier to read? - hopefully you can comment on the Ugo "bits"

but also:

1) is the right way to enable project selection on the Invoice layout via the use of "g1" in tables "Projects" and "Invoice Headers" (ie both are globals with a value of 1) ? or should it be some sort of X join?

2) just a quick question - how would you stop a user from editing a completed invoice? - would it be correct to have another layout for invoice review etc that is read-only, based on another self joined TO based on an Invoice Header status?

ProjLinesInvUgo2.zip

Edited January 12, 2008 by Guest

[comment]

I have redrafted file using those guidelines

No, I'm afraid you haven't. For example, you have a table called Projects - but there is no TO named Projects on the graph, and no layout linked to it. Same with ProjectCosts (which is also a bad name according to my preferences).

As a result - again, as an example only - your calculation field cOpenProjectsID reads:

Case(Count(ProjectCostsForAllProjects::ProjectId) ≠ Count(ProjectCostsForAllProjects::InvoiceHeaderId);ProjectId)

and it is evaluated from the context of AllProjects.





In my version, it would read:

Case ( Count ( Costs::ProjectID ) ≠ Count ( Costs::InvoiceID) ; ProjectID )

which I believe is eminently more readable - and it would be evaluated from the context of Projects. Any other context (other than the base table) would indicate there is some special requirement regarding the context.

I'm not trying to make this difficult for you. It's just impossible for me to wrap my head around your file without spending considerable time and effort. I have already tried twice in vain, so please do not insist. In any case, it is not my place to grant approvals - if you understand the method, you should be able to implement it.

should it be some sort of X join?

Yes. Joins on constants are obsolete since version 7.

how would you stop a user from editing a completed invoice?

First and foremost by account privileges. Any layout tricks would come in addition to that, as purely cosmetic measures.