display report based upon collated field problem

[mattlight]

I have a sales lineage file which creates records from an INVOICES database containing info such as INVOICE NUMBER, SALESPERSONS NAME, INVOICE DATE, QTY, PRODUCT CODE, DESCRIPTION, PRICE, QTY*PRICE, WEEK NUMBER, YEAR and caculation fields that create searchs such as

Salespersons week

calculation is:

salespersons name & "-" & "week number" & "-" & year number

which gives me a field that might say for example

matthew lightbourn-42-2001

In my staff database I select the staff person's name and type in the date to search for and in this database there is an equal calculation as the one above which gives me a matching answer to base a relationship on so that I can view all sales within that week, year and salesperson's name.

It all works but - it also gives me other unwanted info like week 47 which I don't want.

Is there a priority order that is required or can I make it do an exact match? All the setting seem to be correct but can't seem to make it only give me week 42. It seems to look at the relationship and can't be bothered to get every detail the same, weird!

Can anyone help?

NB: The info that I get back is a calculation within the staff database which does a sum all sales by the sales person in that week/year and shows the week number and sales/commission in a brief statement to be handed to staff members so they can see how close to their target they are and how much commission they are getting. It is printed out in a list and I printed 3 example sales staff and one of them said week 47 and the other 2 were correct (week 42) I imagine if it can't get that right then it might also mess up some calculations of what they've sold.

Thanks guys/gals

[ December 03, 2001: Message edited by: Matt Lightbourn ]

[Garry Claridge]

I think the portal will list all records which satisfy the relationship. Not just those which satisfy the search criteria.

You may need to create a new relationship, and hence a portal, which is based soley on the 'name-week-year' calculation field. Hence, you may be able to change the week and year of the source fields in the master record, for the person, and then this portal will change its display of related records.

This is just a guess. If I get a chance I will try it.

All the best.

Garry

[mattlight]

This is what I've done. The relationship is based upon the salesperson's name, week and year field. The search criteria is the relationship and so it should only show related information which includes the name, week and year number and not just salespersons name, any week and the right year.

Should I change the order of the search or will making it week, year, salespersons name give me the right week and year but any old salesperson?

Please help!

cheers....

[Garry Claridge]

Matt,

Use the "Exact" search; i.e. == at the start of the field in "Find Mode".

E.G. =="matthew lightbourn-42-2001"

Hope this is what you are after.

All the best.

Garry

[LeCates]

Hi Matt,

Could you copy and paste the following and post them here?:

1. The local match key

2. The three target match keys (2 correct, 1 incorrect)

FileMaker does have some limitations to the number of characters 'in a row" that it can use for a relationship match. It is possible that the 2/7 of 42 and 47 are being "cut off" - but this is a guess at this point.

Provide the match keys in question, and we'll figure it out.

FWIW, the rule for relationship matches of text strings is this: up to 60 characters may represent the key. Those 60 characters will come from the left (first) words (strings) of a text field. Those words cannot exceed 20 characters.

In otherwords, if you have three strings of 30 characters in a text field, the first 20 characters of each string will be combined for a total key string of 60 characters.

"matthew lightbourn-42-2001" should not be a problem (the space breaks up the string.

"matthew_lightbourn-42-2001 would be a problem (the 2 is the 21st character).

If this is part of your problem, you might be able to solve it by using spaces " " instead of dashes "-" in your key calculations. Or limit the number of characters in a name component string with a Left() function.

Good luck,

[mattlight]

Hi,

The sales database has two field which extracts from 3 other fields.

The 2 fields are called Salespersons Week and Salespersons Month.

The three fields it extracts from are

Salespersons name Salespersons name

Week number Month Number

Year Year

The calculation for Salesperson week is

Salespersons name & "-" & Week Number & "-" Year

giving ie Matthew Lightbourn-42-2001

or in month

giving ie Matthew Lightbourn-10-2001

in the staff database there is a field which takes info from three fields

3 of the fields get their information from one field CURRENT DATE field

you enter the current date or date to search for and it calculates in one field the week number, month number and year number

the field that is used for the relationship is a calculation of Salespersons name, Week Number, year or Salespersons name, Month, Year

which gives you the same result as the field within the sales database which should then display the same information in either the form of a sum or a list within a portal. ie Matthew Lightbourn-42-2001 if the CURRENT DATE FIELD is set to 20 October 2001 and you are on the record containing the salesperson details of Matthew Lightbourn. It works but not entirely accurately.

Is this info any more useful to you?

Thanks for looking into it