FileMaker - Portal issues - how can I correctly extract data

I am having difficulty creating a layout that will display the correct results.
Either my relationships are wrong or I might need to set up different relationships - but don't know how to proceed?

1- I want to see all the commissions displayed in a portal, by staff ID.
2 -Then to see the same above results between a date range - most often by month the commission is due.
3 - Finally for that information to be displayed in a portal - on a tabbed layout with fixed staff id.

(I have a layout I have created - that does not work - which might explain better what I am trying to do) http://www.yuntaa.com/FileManager/Download.aspx?ContentID=4543B66B9722F752E040A8C0030270AB

Jay
moeesamoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lesouefConnect With a Mentor Commented:
I don't know, what is the current date relevant of? it uses year and month in the line items table, but is that based on the delivery date? if yes, it will work.
0
 
lesouefCommented:
incorrect file... downloaded file is corrupted.
you'd better use a layout with a sub-summary based on staffID, then sort per staffID + date_range if the goal is only a report.
0
 
moeesamoAuthor Commented:
Hi Lesouef

I am uploading the file here.

Have had to add the extension .txt so that it will be possible to upload it here.

To view the file please delete the .txt suffix... so that just .fp7 is at the end.

Jay
Portal-Issues.fp7.txt
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
lesouefCommented:
0
 
moeesamoAuthor Commented:
Lesouef - thanks for your help, I have placed the fields as I wanted them to appear, but still have two problems.

I don't understand why the commission layout with the portal has 7 records - that give a unique booking number, 2 or more different invoice months - which should not be possible.

The aim of the layout is so that I can view all of (for example) Staff 1's - commissions for January 2008, then by clicking on the month field and changing it to February, see his commissions for February and so on and so forth, I cannot do that at the moment.

Any suggestions on this please.

I have uploaded the file with a .txt extension, so that it will be possible to upload it here.
To view the file please delete the .txt suffix... so that just .fp7 is at the end.


Portal-Issues-v2.fp7.txt
0
 
lesouefCommented:
> I don't understand why the commission layout with the portal has 7 records - that give a unique booking number, 2 or more different invoice months - which should not be possible.
----------------
because your database is like that, some bookings have more than 1 commission, so the lines_items can be more than 1 for a single booking, but there is no line_item dup for a given staff member. and it displays 4 for staff1, 3 for staff2 , not 7... something I don't understand here?

>The aim of the layout is so that I can view all of (for example) Staff 1's - commissions for January 2008, then by clicking on the month field and changing it to February, see his commissions for February and so on and so forth, I cannot do that at the moment.
------------
then you need to add the a month field to the relationship. use a global field in the staff table to act as a month selector, then add this field to relationships in between staff and line_items, line_items1, etc... same for year
see yr file modified:
https://filedb.experts-exchange.com/incoming/ee-stuff/6767-Portal-Issues-v3.zip
0
 
moeesamoAuthor Commented:
Lesouef... thanks again...

Your modified file gets to display the information as I want it to.
But over the last week I have been trying to implement the example file to the larger working data base, and have been running in to a few problems, partly related to the global fields and the portal not displaying anything. But still trying to trouble shoot that.

Is there a better way of getting this solution to work, with more tables?
Also - sorry to ask - what is a line_item dup you mentioned?

Jay



0
 
lesouefCommented:
dup =  duplicated record.
true, this method is complex because tabs are not meant to be used this way.
using a simple popup to select the current portal records would have been simpler, and work with only one relationship for all staff while here you need 1 for each.
0
 
moeesamoAuthor Commented:
Lesouef -  thanks for your help.

Been away from this for the last month, just getting back to it.

One final question on this one - for some reason your example shows the same date - if i change the date in say Feb for delivery date - why would the other date change?

Jay
0
 
moeesamoAuthor Commented:
Thanks...

Regards Jay

0
 
moeesamoAuthor Commented:
Thanks -
I would have liked to have seen how to recreate the whole solution differently as for some reason errors were still creeping in.
Jay
0
 
lesouefCommented:
I could do it, but this is a support place, and it must stay a marginal occupation, I can't redesign the whole thing though I am able to do it. matter of time.
and the purpose of this site is not to force people to use my way of data organization. I always try to answer within the existing structure, your question was not "how should it be", but "how can I fix that".
For sure using tabs to show the different people is not a good idea, as if any other person is hired, you've got to modify the design.
0
All Courses

From novice to tech pro — start learning today.