The portal show the "wrong" data, what I am missing?

[tomislaw]

Hi, first at all, I am a beginner.

I am sure the problem is trivial, but after yesterday night, my brain is dead.

I have just 2 "core" Tables, "People" and "Overlands".

1. occurrences of the People Table is "Drivers"
2. occurrences of the People Table is "Codrivers"
I connect these 2 Tables called "Drivers" and "Codrivers" via join tables to a third "aggregate" Table called "Cars".
The Cars table is connected to the "Overlands" Table.

When I make a Portal in the "Overlands" Table that display the values from the "Cars" table, and put Fields from Drivers, Codrivers and Cars table, everything is OK, except that I can't show more than 1 Codriver in the Portal. When i put Portals that display values from the Drivers Table, and put fields from Drivers table and Cars table, all records show the same Car. The same thing is when I make a Portal that show the "Codriver" values, and put fields from Codriver and Cars tables, all Codrivers are related to the same car. What I am missing? My brain is melted down.

The File for download the username is the default Filemaker username (Admin) without password.

Thnx for any advice.

[comment]

1 hour ago, tomislaw said:

When i put Portals that display values from the Drivers Table, and put fields from Drivers table and Cars table, all records show the same Car.

This is the expected behavior. When getting data to populate a portal, Filemaker only looks forward, never back.

So when you have a field from Cars_jointable in a portal to Drivers_jointable on a layout of Overlands, Filemaker will populate it using data from the first related record in Cars_jointable. It will NOT go to the corresponding row in Drivers_jointable first, then back to the corresponding record in Cars_jointable. But it will go forward to the corresponding record in People_drivers. 

The solution is to copy the Car field data into Drivers_jointable - either as an unstored calculation field, or as a lookup. Then place this field in the portal instead.

--
P.S. Does one car really have many drivers and many codrivers? I notice that you have ID_driver and  ID_codriver fields in the Cars Jointable table - which would suggest otherwise.

 

[tomislaw]

4 minutes ago, comment said:

This is the expected behavior. When getting data to populate a portal, Filemaker only looks forward, never back.

So when you have a field from Cars_jointable in a portal to Drivers_jointable on a layout of Overlands, Filemaker will populate it using data from the first related record in Cars_jointable. It will NOT go to the corresponding row in Drivers_jointable first, then back to the corresponding record in Cars_jointable. But it will go forward to the corresponding record in People_drivers. 

The solution is to copy the Car field data into Drivers_jointable - either as an unstored calculation field, or as a lookup. Then place this field in the portal instead.

--
P.S. Does one car really have many drivers and many codrivers? I notice that you have ID_driver and  ID_codriver fields in the Cars Jointable table - which would suggest otherwise.

 

The fields are left from the "experimenting" with the solution. Actual it would be ideal if the Portal, with just one portal on the Layout, can display the driver and co drivers (if are any). Maybe is the right way to go with repetition fields in the "Drivers table", where the first repetition represent the driver, and other repetitions the co drivers. Because I have intention to get further with this and implement income/outcome invoices that are related to participants (drivers and co drivers) and overlands. However, at this moment I have thousand questions and just few answers in my head. 
Thnx a lot for your explanation.

[comment]

13 minutes ago, tomislaw said:

Actual it would be ideal if the Portal, with just one portal on the Layout, can display the driver and co drivers (if are any).

That would be possible if you had a structure like this:

People --< Drivers >-- Cars

and a field in the Drivers table to indicate either driver or codriver. 

There may be an even better solution overall - but for this we would need to understand what these entities represent in real life, and what do you intend to accomplish by tracking them. For example, I have no idea what "Overlands" are.

 

18 minutes ago, tomislaw said:

Maybe is the right way to go with repetition fields in the "Drivers table"

I don't think you will get very far with this idea.

[tomislaw]

1 hour ago, comment said:

That would be possible if you had a structure like this:

People --< Drivers >-- Cars

and a field in the Drivers table to indicate either driver or codriver. 

There may be an even better solution overall - but for this we would need to understand what these entities represent in real life, and what do you intend to accomplish by tracking them. For example, I have no idea what "Overlands" are.

 

I don't think you will get very far with this idea.

Hi,

thnx for spending your time on my problems. I just started my small (one man band) business for Off road travels (guiding). I run my it on the spreadsheet, but this will not meet my needs in long term run because lack of structure. In the former company where i worked, as a long time filemaker solution user, I got a copy of filemaker (18) when i was leaving the company. So I am now in the learning phase and I try to make a solution for my needs.

Also, the main idea is to build an app for my Off Road travel company. Because I charge per car and people in the car, I need to be able to put so many people in to the car as needed. Because I work with people, and not with cars, I decided that my "main table" should be the "people" table, and not the "cars" table. And in the car is always at least one person, the driver. When I make trips (overlands), I add drivers from the Address Book (people table) and when necessary codrivers. 

My workflow idea is:

1 enter people in the address book
2 make an overland
3 put people from the address book (grouped by cars) in to the overland
4 in the "overland" layout, select an driver (customer) and make the invoice for him

of course, I am just on the beginning with the developing (and a newbie), there will be a lot to do in the future. At first, make an income/outcome table so I can make invoices, reports per clients (drivers), per overlands etc.

But at first, my wish is to make the structure right, and after this, I can play wit the details and features.

Also, thanks again, even for reading about my problems and for helping... if you ever come to Croatia, i owe you at least a (lot of) beer.

[comment]

I would like to understand this better: 

• Are all the people in cars your clients? Or do you employ your own drivers?
• Same question about cars: are these your cars? Or is it just a trip to XYZ, that anyone with their own car can join?
• What if a customer comes with a group of 2 or more cars?

[tomislaw]

26 minutes ago, comment said:

I would like to understand this better: 

• Are all the people in cars your clients? Or do you employ your own drivers?
• Same question about cars: are these your cars? Or is it just a trip to XYZ, that anyone with their own car can join?
• What if a customer comes with a group of 2 or more cars?

1. Just one person from a car is the client, I do not employ drivers, but i ned (at least I think so) the person count and person separation because i charge by car for guiding, and per person because there are cost by person, let say, meals, and every person can take a meal of different price.
2.  Clients come with they own cars
3. I didn’t think about it at all.  But of course, it is possible. For example, that one company sends its employees to team building. And in that case, the company will pay the full amount, for all vehicles and all participants. But either way, names of all participants, not just their number, are mandatory because of the law.

Edited February 20, 2022 by tomislaw

[Steve Martino]

I think your main table should be TRIPS.  Trips are the business.  People get in cars that have drivers that go on trips.  Think of TRIPS, like an Invoice table that use a 'line items' join table, pulling in date from People, & Products.
Sure they'll be  PEOPLE, LOCATION, CARS, etc. tables too.

Edited February 20, 2022 by Steve Martino

[tomislaw]

1 minute ago, Steve Martino said:

I think your main table should be TRIPS.  Trips are the business.  People get in cars that have drivers that go on trips.  Think of TRIPS, like an Invoice table that use a 'line items' join table, pulling in date from People, & Products.
Sure they'll be  PEOPLE, LOCATION, CARS, etc. tables too.

Thnx, I never did think in this way. I will give it a try. I am on the beginning, so I have nothing to mess up 😁  

[comment]

On 2/20/2022 at 10:43 PM, tomislaw said:

i charge by car for guiding, and per person because there are cost by person,

If I follow this correctly, you have a structure of

Tours --< Groups --< People

where each group has one car and one person as the client. If these conditions are always true, then you only need a field (or two) in the Groups table to record the car's details, and another field to select the client's ID. If a group can have more than one "client" then you need to designate them as such in the People table.

I don't see a need for a join table between People and Cars/Groups, unless you have many repeating customers and want to avoid re-entering them for each tour they take.

 

Edited February 22, 2022 by comment

[tomislaw]

1 hour ago, comment said:

If I follow this correctly, you have a structure of

Tours --< Groups --< People

where each group has one car and one person as the client. If these conditions are always true, then you only need a field (or two) in the Groups table to record the car's details, and another field to select the client's ID. If a group can have more than one "client" then you need to designate them as such in the People table.

I don't see a need for a join table between People and Cars/Groups, unless you have many repeating customers and want to avoid re-entering them for each tour they take.

 

Hi,

 

I did make join tables (repeating customers). I used the join tables just to make a relation so i can make reports based on various criteria, but all fields are populated by Lookup values or scripts to avoid data changes in the "Overland table" if, let say, a customer change the address, phone etc.
Again, thnx very much, you helped me a lot.