• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Updating a Field Automatically with a Field in another Record

Here is the problem, i am fairly new to filemaker, i have to create a database with a special level system, just say i have a field "sponser", and a field "level" just say the record that need to be updating has a level of "3" the way that it works is, the sponser is the name of the business that is 1 level below the current record, so i need to get from another record with the level of "2" the "Name" and have it update to "Sponsor" on the record with level "3", i think i explained it alright.
0
MA9N3TO
Asked:
MA9N3TO
  • 4
  • 3
1 Solution
 
MA9N3TOAuthor Commented:
This is an example of what i mean:

Record 1

Name = Business1
Sponsor = N/A
Level = 2

Record 2

Name = Business2
Sponsor = (Needs to Be) "Business1"
Level = 3

Need to somehow make Record 2 "Sponsor" find the record that has level "2" and get the Name "Business1" and put it in Sponsor of Record 2
0
 
mrpez1Commented:
I assume that each business has only one level and one sponsor that will always remain static. Can more than one business be at level 2 for instance? Can a business be promoted?

If the level field will be unique and static, you need to make a self join relationship b/w the new record and level number. You'll need a field in each record to tell the self join which level to associate with. I.e. a stored calc that shows (Level - 1) that is related to level in the same database. Have the sponsor field be a lookup (assuming the sponsor can't change) based on the SJ relationship.
0
 
MA9N3TOAuthor Commented:
thanks,

more than one business can be on a certain level, however they do remain on that same level,
and the sponsor for each business doesnt change

can you explain more clearly what you mean by self join
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mrpez1Commented:
A self join relationship is when you creat a realtionship b/w a database and itslef. You relate a record to another record (or set of records) in the same database. What I posted won't work in this situation though b/c it will only find the first person of a level one less than the new record's level. You need some way to tell the new record which previous record is the sponsor.

e.g.:

Record 1

Name = Business1
Sponsor = N/A
Level = 1

Record 2

Name = Business2
Sponsor = Business1
Level = 2

Record 3

Name = Business3
Sponsor = Business1
Level = 2

Record 3

Name = Business4
Sponsor = ?
Level = 3

How will record 4 know whether Business2 or Business3 is the sponsor? You'll need to enter it manually. If there was only one level 2 business than you could do what I posted earlier.

Hope that helps.
0
 
MA9N3TOAuthor Commented:
thanks,

the way it will be set up is, just say business1 sponsors business 2 and 3, from the example you have shown, business3 would be the sponsor for business 4,

can u think of a way of doing this?

0
 
mrpez1Commented:
You need a script to do this. You'll also need a global field in the database.

Here are the fields:

Company_name - text field
sponsor - Calculation (Case(IsEmpty(sponsor_number), "N/A", SJ_test::compnay name))
record_number -number (auto enter serial)
Level - number
_grecord_number -global number
sponsor_number - number

You'll need a self join relationship (to the same database):

sponsor_number =::record_number

 When the script is run from the sponsor company's record, it will do the following:

set field [ "global field', "record number"] This sets the global field to the sponsor's record number
New Record/request
set field ["sponsor number", "global field"] Now the sponsee's (?) record has the sponsor's record number
show custom dialog ["Enter company name", "company name"] This will set the name for the new company
Set field ["Level", "SJ_relation:level +1" This sets the sponsee's level to the sponsor's level + 1

0
 
MA9N3TOAuthor Commented:
thanks bro, that helped me out heaps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now