Jump to content
Rikki

Relationship help

Recommended Posts

Hi guys,

Sorry I do not know the terminology of what I need.

I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type.

Eg:

Supplier    - Lead Type - Postcode - Count

Supplier A - Back Data - AB            - 20

                                     - BB            - 155

                                     - CB            - 30

 

                     Profile     - AB             - 55

                                    - BB             - 27

My problem is with the way I have set the relationships up currently. When I add a count for a postcode it adds that count for all suppliers. I need the postcode count to be specific to each supplier and lead type.

Can anyone tell me what I need to do to get this working correctly? I have attached a screenshot of my relationships.

Thanks

relationship.jpg

Edited by Rikki
mistakes

Share this post


Link to post
Share on other sites

This sounds more like a report than a database to me.  That would mean that you don't need new fields in the Postcode table.

What is not clear to me in your structure: in what table/TO do you store the actual lead?  I'm assuming that the 'lead' table stores what supplier, what type and what postcode it is?

Share this post


Link to post
Share on other sites
15 hours ago, Wim Decorte said:

This sounds more like a report than a database to me.  That would mean that you don't need new fields in the Postcode table.

What is not clear to me in your structure: in what table/TO do you store the actual lead?  I'm assuming that the 'lead' table stores what supplier, what type and what postcode it is?

I can understand how you see that. I will try to explain a little better.

The database I am working on uses a new external file for each table. The leads table is actually in a different file and isn't referenced from here (the suppliers database) the count for each postcode isn't based on leads currently in the database, it is actually the count that the supplier is able to provide but we haven't yet purchased. In my screenshot the __Default table is actually the supplier database table.

Share this post


Link to post
Share on other sites

I don't quite follow I must admit.  If you need to report on the leads table but it is not referenced in the file you sent us the screenshot from then how are you planning on getting to that leads data?

The closest I see this coming together is in that bottom row of TOs.

But the "supplier lead type join" table would need an fk for the post code on each record and that then would become the source TO for your report.

The "lead type postcode join" would go away.

Share this post


Link to post
Share on other sites
14 minutes ago, Wim Decorte said:

I don't quite follow I must admit.  If you need to report on the leads table but it is not referenced in the file you sent us the screenshot from then how are you planning on getting to that leads data?

The closest I see this coming together is in that bottom row of TOs.

But the "supplier lead type join" table would need an fk for the post code on each record and that then would become the source TO for your report.

The "lead type postcode join" would go away.

I don't actually need to report on any data in the leads table for this purpose. The supplier will send me a list like this:

Postcode Count
AB 528
AL 222
B 1062
BA 408
BB 492
BD 426
BH 600
BL 342
BN 828
BR 264
BS 882
BT 1548
CA 492
CB 414
CF 1062
CH 618
CM 528
CO 582
CR 288
CT 438
CV 744
CW 276
DA 348
DD 324
DE 810
DG 264
DH 330
DL 396
DN 882
DT 240
DY 600
E 210
EH 678
EN 192
EX 684
FK 174
FY 390
G 648
GL 546
GU 528
HA 342
HD 252
Hg 102
HP 384
HR 276
HS 96
HU 522
HX 204
IG 240
IM 90
IP 618
IV 216
KA 414
KT 348
KW 120
KY 432
L 420
LA 312
LD 78
LE 1146
LL 678
LN 360
LS 534
LU 306
M 510
ME 534
MK 420
ML 354
N 198
NE 1422
NG 1320
NN 744
NP 594
NR 894
NW 150
OL 294
OX 552
PA 342
PE 1140
PH 138
PL 660
PO 948
PR 660
RG 624
RH 432
RM 462
S 972
SA 852
SE 408
SG 330
SK 474
SL 240
SM 150
SN 450
SO 540
SP 192
SR 210
SS 498
ST 642
SW 222
SY 432
TA 444
TD 138
TF 204
TN 726
TQ 282
TR 474
TS 816
TW 312
UB 240
WA 666
WD 150
WF 432
WN 408
WR 342
WS 492
WV 498
YO 816

 

My goal is to import these counts from an excel spreadsheet into the database so I can keep track of these counts for each of the 4 lead types for each supplier.

Edited by Rikki
mistakes

Share this post


Link to post
Share on other sites

Importing that is straightforward, just add the supplier id to that and you're done.  But I don't see where the lead type comes into play with that data then...

Share this post


Link to post
Share on other sites
5 minutes ago, Wim Decorte said:

Importing that is straightforward, just add the supplier id to that and you're done.  But I don't see where the lead type comes into play with that data then...

This list was just an example of one "type" there can be 4 "types" but they all have the same sort of data just the source is different so I need to be able to distinguish between the 4 types of lead count (back data, profile, survey & 2nd use) there are around 30 suppliers in the table and 150 postcodes. I need all 30 suppliers to be able to track 4 different lead type counts.

Share this post


Link to post
Share on other sites

I still don't understand the issue.  If you import that data, and at the moment of import you add the supplier ID and the lead type ID for the imported set then in effect you have a good table of of lead #s per supplier per postcode.  Once you have that, is that enough or do you need to do something with the data.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Hproth
      I'm sure there is a simple solution to this problem, but I can't seem to figure it out!
      I have a custom app that allows the user to write letters amongst other things. 
      Each patient is linked to a GP and a Consultant, when writing a letter to a patient there is the option to CC both the linked GP and Consultant. However, occasionally another GP or Consultant might need to be CC'd in the letter (in addition to the ones already linked to the patient). So I would need the address of this additional consultant/GP to also be CC'd in the letter. For the life of me I can't work out a simple way to do this. 
      Does anyone have any suggestions?
      Thanks in advance.
       
    • By fumblewinter
      The Problem:
      I have a filterable portal (1) displaying contact names from a staff table. I need to click on a contact to add them to a separate portal of invitees (portal 2)  on the same layout (Events) . I then need the original portal to either hide (or in some other way denote) the people already added to the second portal. In other words, it needs to be clear that a person already invited can't be invited again.
      The layout is from the table: Events
      Portal 1 is showing records from Staff (filterable via a global search field)
      Portal 2 is showing records from EventInvitees
      I need Portal 1 to only filter through those staff who haven't been invited yet.
      Any help would be gratefully received.
      I am struggling around using 'conditional formatting', 'hiding when…' options and non-matching field relationships!!
      Thanks in advance!
      Fumblewinter
    • By Lowermountain
      Hello,
      New to Filemaker and new to this forum. Hoping someone can help me out.
      I am working on a personnel planning system with, among others, the tables Employees, Shifts and Availability. 
      tblEmployees has fields like Employee ID
      tblShifts has the fields Employee ID, Start Timestamp, End Timestamp
      tblAvailability has the fields Employee ID, Start Timestamp, End Timestamp and Status
      When in the layout Shifts I want to open a portal in a popup and I want this portal to be sorted by Availability and I want the portal rows with employees to be 'greyed out' when status = 0, green when status = 1 and normal when no Availability is given.
      I basically need to know which records from tblAvailability match records from tblShifts and then which records from tblEmployees match records from tblAvailability.
      How do I go about this? Do I need SQL?
      Hope this is a somewhat clear question. Many thanks!
    • By HJS
      L&G,
      I have read the guidelines for posts in this forum. Therefore I did not enter the Post title that came first in mind: Do I understand portals (aka do I understand Filemaker) and I do not emphasize now on being new in Filemaker issues.
      I am trying to achieve something simple at a first step of a big plan: Entering recipes in a Filemaker testversion database. 
      The longterm goal should be a database in which I can enter my collected recipes.
      Followed by tool for a weekly menu plan for my family and a shopping list coming out of the database after taking care of my fridge's stock => so far so good, but currently this is a plan for the next ten years I realized starting with Filemaker.
      I searched different apps and database programs, and Filemaker seems to be my solution as I did not find anything that fits exactly my expectations.
      So back to the first problem: how to enter my recipes?
      I created a table of recipes with an unique ID_pk (I learned already about pk and fk), Name and How-To-Make-it as well as IDs(fk) from the other tables which are Zutaten (Ingredients) with the name and the amount, Form_Zutaten (how the ingredients are used i.e. chopped, sliced, etc.) and Einheit_Zutaten (unit i.e. cup, liter, etc.). Why I have choosen this structure: because all ingredients must be combinable with different units and how the are used. otherwise I have to enter e.g. avocado sliced, avocado mashed, etc. or make rules like liquids can only be liter or mililiter or cups while flour can only be gramms etc.
      So a combination of all three tables should be possible.
      What I though might be an easy task is to create a recipe layout than with 
      recipe ID
      recipe name
      and a dynamic portal depending on the number of ingredients constisting of:
      Zutat - Form - Menge - Einheit
      in english: Name of the ingredient - how is it, which kind/form - how much (qty) - which unit
      so I related the tables via pk and fk, created the layout and bam: complete disaster.
      after needing a couple of days to make the values appearing in the dropdown (learned about the value list), some fields in the portal do show the all entries of the ingredients, while the kind/form and unit fields do always show the first value of the table?
      and although I locked the fields, the first entry is always shown and is getting overwritten by the choice I choose from the dropdown...
      and nothing is dynamic, meaning that 7 ingredients will make 7 lines and it always starts with one empty line, etc.
      I am not sure if my descriptions do make sense, therefore I am attaching my work, hoping someone could help me with how to use the portal for a convenient data entry.
       
      thx in advance!
      br
      HJS
       
       
      2017_09_Essensplan.fmp12
    • By 123
      Hey,
      I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not.
      I already have the following tables:
      Restaurants, Employees and Meetings
      So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, which allows me to show all employees that work at a selected restaurant using a portal on the meetings layout. How do I proceed to solve my problem? Do I create another table MeetingAttendees or something similar that I use to keep track of people attending a meeting? How would I create a relationship then to allow me to mark certain employees as present and absent?
      Thanks in advance,
      Mike
×

Important Information

By using this site, you agree to our Terms of Use.