Jump to content

Cannot get relational fields


Recommended Posts

I have two files. One is the parent/student file. Has a parent record and student records. The second file is another student file. I want the second file to get relational fields from the first file. Now, in the first file, the student record gets the last name from the parent record. To link the two files, I create a field of last and first and grade. Same in the second file. Then I want to pick up fields from the first file into the second. It didn't work. So I tried to index the field but it won't because last is a related field from the parent. I was going crazy so in the student file I wrote a script that copied each last name into a last_copied field so that the field of last, first and grade no longer had related fields and with setting indexing (so it shouldn't be unstored) it worked.

This is a terrible way to have to do this. I'm hoping there's a basic answer to this that I just don't know. Anyone? I'd appreciate it.

Michael

Link to post
Share on other sites

First, it needs to be noted that matching by names is very problematic, because two students in the same grade could easily have the same name. Ideally, you would use some attribute that is guaranteed to be unique to each student.

I am also surprised by your arrangement where "the student record gets the last name from the parent record". I would have thought there are quite a few cases where a child (or a ward) has a different last name from its parent or guardian.

Now, you didn't say why you need to have a second file. Assuming that this file's data is imported from another system, and that you have no way to include a unique ID that would be common to both systems, I would suggest you make the last name field in the students table lookup its contents from the parent record. Then it can be stored and used as a match field for the relationship to the second file. However, you also need to have a mechanism to perform a relookup if the parent's last name is modified.

 

 

Edited by comment
Link to post
Share on other sites

Thank you.

In my case they all have the same last name. The records are differentiated by last+first+grade. This is a one time shot that we need to do. I actually tried the lookup from the second file, but I see what you're saying.

Am I to assume you can't set a common "key" between two files if some of the key is a related field?

Link to post
Share on other sites
22 minutes ago, mleiser said:

Am I to assume you can't set a common "key" between two files if some of the key is a related field?

You cannot define a relationship between two tables (in the same file or in two different files) using match fields from other tables.  In  addition, the match fields on the "queried" side must be indexable (.i.e. not global fields and not unstored calculation fields).

In the given example, you could use an unstored calculation field in the students table (of the first file) as a match field - but then you would have to view the related data from the second file in a layout of this table, not the other way around.

 

 

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 ggt667
      Is there a way to query CWP using FMPXMLRESULT as payload to create new records in FileMaker? Goal is to be able to insert records from command line using curl from FMPXMLRESULT files to avoid a somewhat clunky query from FileMaker XML import.
      I tried the following:
      cd /tmp/ curl -X POST -kL -o /tmp/findany.fmpxmresult.xml "user:pass@127.0.0.1/fmi/xml/FMPXMLRESULT.xml?-db=PushTest&-lay=Table&-findany" curl -X POST -kL --data @findany.fmpxmlresult.xml "user:pass@127.0.0.1/fmi/xml/FMPXMLRESULT.xml?-db=PushTest&-lay=Table&-new" A new record is created, however the payload is ignored.
      Payload below FTR
      <?xml version="1.0" encoding="utf-8" standalone="no"?><!DOCTYPE FMPXMLRESULT PUBLIC "-//FMI//DTD FMPXMLRESULT//EN" "http://127.0.0.1/fmi/xml/FMPXMLRESULT.dtd"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="03/16/20" NAME="FileMaker Web Publishing Engine" VERSION="18.0.4.428"></PRODUCT> <DATABASE DATEFORMAT="MM/dd/yyyy" LAYOUT="Table" NAME="PushTest" RECORDS="17" TIMEFORMAT="HH:mm:ss"></DATABASE> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="name" TYPE="TEXT"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="number" TYPE="NUMBER"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="date" TYPE="DATE"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="timestamp" TYPE="TIMESTAMP"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="time" TYPE="TIME"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="_global" TYPE="TEXT"></FIELD> </METADATA> <RESULTSET FOUND="1"> <ROW MODID="13" RECORDID="24"> <COL> <DATA>Sweet</DATA> </COL> <COL> <DATA>667</DATA> </COL> <COL> <DATA>06/05/2020</DATA> </COL> <COL> <DATA></DATA> </COL> <COL> <DATA>10:44:15</DATA> </COL> <COL> <DATA></DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> If I simply put the parameters after the "?" in the payload file it works, however INSERTing the actual FMPXMLRESULT would be desirable.
      curl -X POST -kL --data @/tmp/GETlike.txt "user:pass@127.0.0.1/fmi/xml/FMPXMLRESULT.xml" Where /tmp/Getlike.txt contains
      -dbnames as pr example returns expected list of database names.
    • By Msaeed
      Hi All,
      I would like to find solve my issue, as mentioned on the topic of how can find more than fields like serial No. or Code by IOS camera. please find below the script.
       
       
       
      thank in advance for any help or support  
    • By tomp
      I'm having a problem getting BE_SMTPsend to successfully send an email with an attachment.
      No problem sending without an attachment, but when I add an attachment, I get an error 2 (not sure exactly what that means, but without the attachment, no error)
      These are the argument strings with and without attachments:

      I have tried both relative and direct file paths for the variable $attachment. Examples are:


      Both of them return an error code of 2.
      Since the 'send' without an attachment works, all the values of the fields/variables other than $attachment must be valid.
      Can anyone offer a suggestion as to what might be going on and what I might try to resolve this?
       
    • By Msaeed
      Hi All,
      As per mentioned on topic, in fact i would like to create icon ex. hour in case rigging guard the insert time.

    • By Solvax
      Hi,
      I'm trying to make a subsummary report with data from the current table and a related table.
      I never get to display the correct totals for the related table, and would therefore appreciate your help.
      First I'll try to explain the workflow:
      I have two tables called 'Voorraad_uit' (= outgoing stock) and 'Voorraad_in' (= incoming stock) which are related by delivery number.
      Data is first entered in 'Voorraad_in' (= incoming stock) and then related records are created in 'Voorraad_uit' when an incoming stock item is partly or completely shipped.  This second table contains mainly the outgoing amount and a unique outgoing delivery ID.

      As a consequence, one record in table 'Voorraad_in' (= incoming stock) can have one ore more related records in 'Voorraad_uit' (=outgoing stock).
      I want to make a subsummary report showing the total amount of incoming and outgoing units per product(name) by using the following layout structure:

      I tried two scenarios which each give me a part of the desired result.
      when I base the layout on 'Voorraad_in' (=incoming stock): I get the right totals for incoming deliveries I get all the incoming deliveries listed (also those without outgoing delivery) Body part 3 (see above) only shows one row also when there are more than one related outgoing transactions I get the wrong totals for outgoing deliveries (only based on one record) when I base the layout on 'Voorraad_uit' (=outgoing stock): I get the wrong totals for incoming deliveries I don't get all the incoming deliveries listed (only those with outgoing delivery) Body part 3 (see above) shows all the related outgoing transactions I get the right totals for outgoing deliveries Main goal is having the right totals for both incoming and outgoing deliveries..
      My layout is sorted ascending by (in mentioned order) productname, incoming delivery number and outgoing delivery number.
      The totals are calculated by using summary fields.
      I hope I'm on the right track and that the desired result is possible.. your help is much appreciated!
      Thanks in advance for your help.
      Regards,
      Solvax
  • Who Viewed the Topic

    1 member has viewed this topic:
    getmikek 
×
×
  • Create New...

Important Information

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