script or calculation ?

[George TOUBALIS]

Hello from Greece (sorry for my not so good English)

I have a line item file for tracking jobs in city's

I have separate

person.fp5

jobs.fp5

city's.fp5

in different files for good normalization

I fill the line item fields via script and portal ...so far so good ....my line item file is light and speedy

1st Rule: one person or more can do one or many jobs in one or more city's

2nd Rule: in one city there can be one or many jobs

The line item file has:

person FK ID (P, P1, P2, ...)

jobs FK ID (J, J1, J2, ...)

city's FK ID (C, C1, C2, ...)

...and of course Line item pk ID

A typical situation is:

Record 001 City Paris Person P job J

Record 002 City Paris Person P1 job J

Record 003 City Paris Person P2 job J1

Record 004 City Paris Person P2 job J2

Record 005 City Paris Person P3 job J14

Record 006 City London Person P9 job J

Record 007 City London Person P11 job J1

Record 008 City London Person P12 job J1

Record 009 City London Person P13 job J1

Record 0010 City London Person P3 job J14

Record 0011 City London Person P17 job J3

All i want is to collect in one text field the persons who only involved with specific Jobs in one city (Jobs FK id : "J" & "J1") I don't care I don't want jobs with fk ID different from "J" & "J1"

Project Director Fk: J

Project Coordinator Fk: J1

(this jobs I need to collect for one city : Project Director & Project Coordinator)

Example For City Paris:

(Project Director: Person P, Person P1 - Project Coordinator: Person P2)

For City London :

(Project Director: Person P9, - Project Coordinator: Person P11, Person P12 and Person P13)

I send this question in another forum and folks give a solution of a list of person who work these spesific jobs in spesific city but I don't want a list.

I want for one city only one field with the information (Project Director: : Person XXXX, person XXX and person XXX - Project Coordinator: person XXX , person XXX and person XXXXXX )

I want this calculation for export work....

sorry again for my not so good English

anyone...:?

please...

[McCormick]

I think it is difficult because you need to turn it "sideways". You have data organized by each person, and you want to change it to be organized by Job Title (for example). I think you can only do this with a script. You can make some Global Text fields. The script would start by asking you what to search for. Then it would do the search. Then it would go to each record (using Loop) and add that information to the Global field. Example:

[You already searched and found everybody you wanted]:

Go to Record [First]

Loop

Set Field["GLOBAL NAME", "GLOBAL NAME & ", " & Name]

Go to Record [Next]

End Loop

Or you could use Paste instead of Set Field. After this part of the script, the field will have the names of every record you found: "Person1, Person2, Person3".

You can make different Global fields for each category (Name, City, Job). If they are always the same, you could make one field for each Job Title (GLOBAL PROJECT DIRECTOR, GLOBAL PROJECT COORDINATOR), but that is a lot of fields. I think instead, you would do one search at a time and export that field to another location: a spreadsheet or another FMP file. If this would be a lot of searches and exports, then the script should do them, but it will be one at a time, and it will be slow.

Hopefuly that is some help. Perhaps someone else has a faster solution.

[George TOUBALIS]

Well look... I want only

PROJECT DIRECTOR and PROJECT COORDINATOR

I have about 400 jobs (PROJECT MANAGER, SOUND MANAGER, ETC)

But in my report I want only PROJECT DIRECTOR and PROJECT COORDINATOR nothing else...

By the way in any of my reports the no of the citys is maximum 110 so

the minimum no of "PROJECT DIRECTOR and PROJECT COORDINATOR persons is 220.

General 1-2 or 3 persons are

PROJECT DIRECTOR or PROJECT COORDINATOR in one city

Thanks any way...

[Ugo DI LUCA]

Hi George,

If I understand, you want to parse the content of several lines in your Jobs Line Items into a new text field.

This could be done as soon as you would have "linked" the job ids you want to parse together.

Then, first you'd need to exclude from any "search" those records that aren't either "Project Director" (J) and "Project Manager" (J1).

In the Line Item, create these fields and calcs :

1. c_PDorPM_Match => Job = "J" or Job = "J1".

[color:"red"] This will tag those records with a 1, all others with a 0

2. c_PDorPM_Identified = Choose(c_PDorPM_Match,"",Case(Job = "J",0, Job="J1",1)

This calc will tag the Job J (Project Director) wih a 0 and Job J1 (Project Manager) with a 1, which [color:"red"] will be used as a sortkey later on to make sure we'd be parsing the Project Director first.

3. As you want to group these records by "City", create another calculation,

c_Concanation = City& "-"&c_PDorPM

4. Finally add a global field called g_parsed (text).

For the rest of the solution, I'd assume you'd be doing the job from another file, which I'd call MainReport.fp5.

In MainReport.fp5,

1. Go into the Menu 'Define Value List' and create a new List (if you don't have it) called 'CityList', that you'd create using the option "by field values", targetting the 'City' field in your Line Item File.

2. Create a global text field, called g_CityToParse.

3. Create a global num field called g_Match (populated with value = 1)

4. Create a calculation c_ParsingConcanation = g_CityToParse& "-"& g_Match

5. Create a relationship called 'ParsingRel' using the c_ParsingConcanation at Left side (MainReport.fp5) and the c_Concanation for Right side (LineItem.fp5).

Use the c_PDorPM_Identified as your sortkey for the relationship and make sure the right side calculation is indexed

6. Create a global field called g_counter.

7. Create a text field 'JobsAndCitiesConcanated'

8. Create a text field 'City'

Let's go for the script now. Obviously, I didn't tested it, so it may happen to be buggy.

GoToLayout

GoToRecord[First]

SetField[g_counter,0]

Loop

End Loop If [g_counter = Patterncount (ValueListItems(Status(CurrentFileName),"CityList"),"

[George TOUBALIS]

I will test it ASAP

You are really speady....

thanx

[Ugo DI LUCA]

Actually, may be too quick.

You could do it by calc if you're not worried about having crossed relationships calcs along your line items.

This would be done by a selfJoin, involving quite the same relationship and sort key as the one I suggested.

Then, you'd have to build 2 sets of Related Value Lists (one for the Name, one for the Job).

As the second one would probably hold 2 values only (J and J1), and J (diector) should appear once only; you could use it or not.

Then the calc(without ValueList2) would be something like :

c_RelatedRecordsAsOneLine = City& ":"& ::SelfJoin:Person & "::"& "Project Director" & Case(Patterncount(ValueListItems(Status(CurrentFileName),"PersonList"),"