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.
MA9N3TOAsked:
Who is Participating?
 
mrpez1Connect With a Mentor Commented:
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:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
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.

All Courses

From novice to tech pro — start learning today.