Hi all,

This is a really noob question but hey...

I'd like to be able to produce a report of all quotes or invoices produced between 2 user specified dates, inclusive.

Is there a simple script that I can adopt or will I need to pull more of my hair out? LOL

Many thanks for your guidance on this one.

See attached demo

a bit brief - but shows principal

the alternative to the GTRR (go to related record) script step is to do a find...etc

HTH

Simon

PS You also don't need the enter browse mode command after the close window ...

InvoicePortalDemo2.zip

Edited September 6, 200817 yr by Guest

That's cracking. thanks so much for your effort and time. I'll have a look at it now in more detail and let you know how I get on.

Cheers

Rich

Well, I've reviewed the file and pulled it to bits. Now I have created 2 new fields in my timesheet table; report from and report to, both being Globals. Then I created a self relationship linked to the timesheet date field. This seems pretty much what the demo did as far as I can tell.

Thing is, i created a portal linked to the new table occurrence and it is just not showing the records. While I'm on, I also need to be able to search for all records by a particular client number....just to make things easier! LOL

Thanks again for the help

Rich

*Edit* After Additional investigation the portal will only show the first timesheet record, coincidentally dated the first day of the report criteria. When I expand the criteria, nothing comes out...

Edited September 7, 200817 yr by Guest
updated

I think perhaps I need to be a little more clear about what I wish to achieve...

I have several tables: CLIENT, TIMESHEET and INVOICE for the sake of argument. CLIENT stores all client information, TIMESHEETS is where their timesheets are created (how's that for clear ) and a so far unused INVOICE table. All tables are linked by unique CLIENT ID.

I can create timesheet records for the client fine, but I need to be able to pull all timesheets per client, calculate the totals between two user specified dates and then store all the information in the INVOICE table, after producing an invoice. This may sound odd, but the timesheets are to be kept separately from the invoices. Invoices are simply to have the user specified date range and the totals to be paid on them.

There - is that any help? I feel that I am *this close* to solving this but you guys may have a more elegant solution to this requirement.

Many thanks

Rich

Well, if you create a new invoice with ClientID, StartDate and EndDate, it should already "know" which timesheets belong to it - provided you have defined a suitable relationship.

Note that this may pose some problems if users create timesheets "retroactively" (i.e. dated back to the period that has already been invoiced).

Having developed a similar system, perhaps the way we went about it might help.

A staff member created a timesheet or entered an expense (travel, supplies, whatever might be charged back to the client). All these expenses went in the COSTS table. Each record had an amt, staff ID, ClientID (actually a JOBID, too).

Then, on the Invoice layout, we had a portal that displayed any costs that had yet to be invoiced. The user clicks the COSTS portal's Add to Invoice button, and it becomes an InvLI, and the COSTLI receives an InvLI_ID. We actually allowed a cost to be split across several invoices.

So, if you put add to the filter on the Costs portal (it's already filtered by ClientID), it'll limit the costs to a date range.

I am not sure I understand what your next question is...

I think the first thing to do is to get your portal working - you need to check the relationships again... (assuming the portal is on the main layout - either repeated per record or in the footer as per demo file)

The second thing is to add in a client ID - once you can see how to filter by client id as well as date (by extending the relationship and a third global etc)...

Once you have got these two things sorted you will be able to move on to the real solution

Simon

Thanks guys,

This is baffling me. I have tried everything to get the relationship working but nothing is happening.

I'm sure it is to do with the relationship. Do both tables need identical fields in order for a portal to work? I have created relationships between start date, end date, customer ID and nothing happens. Let me check the portals forum for more info and get back to you.

Thanks for the assistance.

Nope - still baffled. I'm under a lot of pressure to make this work and I'm frustrated by the lack of decent documentation that comes with the software...thank goodness for you guys.

My ReportFrom, ReportTo and Client ID are all globals in both tables, and so far I am assuming that this is all I need to do to get it to work. The other thing is where the portal should be - which table should the layout be tied to?

Your help is appreciated, and all this hair pulling is saving me money on a barber.

Rich

My ReportFrom, ReportTo and Client ID are all globals in both tables

I am not sure that's a good idea, in view of your clarification. Take a look at the attached example for a possible approach I mentioned earlier.

Note that this is very basic - but you have start somewhere.

Timesheets.fp7.zip

wow thanks for the help. I've reviewed both the example files and I think I understand what I need to do, but for the life of me this isn't working.

I have taken the liberty of attaching the project file here if any of you want to spend some time having a laugh at my efforts. I don't expect anyone to do the work for me, I'm more interested in why this isn't working so I learn.

Thanks again.

Rich

Invoices_Timesheets.zip

Try reversing the ≤ and ≥ operators in the relationship's definition.

Comment - you, my friend, are a genius. I'd have spent an age looking for that.

Thanks to all for your support - this forum adds infinitesimal value to Filemaker software. (and no, don't ask me to write a formula for that LOL)

Rich