Jump to content
Sign in to follow this  
Hproth

Multiple related fields in a record from the same table

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites

Well you left out a lot of details about structure and work flow, so it's hard to give a definitive answer.

When you are drafting the letter, how do you get any of the GP's/Consultants on the CC list?

You may need a popover with a picker list, probably another relationship, that shows all the GP's/Consultants who aren't already on the CC list, to add with a script.

Share this post


Link to post
Share on other sites

Hi Steve, thanks for your answer - i'll try provide some more info below with a better explanation.

There is currently a relationship between the patient and GP table. So each patient can be linked to one GP. 

Then there is also a relationship between the letter page and the patient. So when drafting a letter - you select the patient. There is then a radio button that states CC GP "Yes" or "No". If CC = "Yes" then the address for the GP that is linked to the patient appears at the bottom of the letter. This is displayed via a merge field on the letter.

However, I sometimes need to CC another GP into the letter - in addition to the one that is already linked through the GP - Patient relationship.

 

Hope this clears a few things up.

Share this post


Link to post
Share on other sites

I guess you could either assign the CC's first, or use the picker layout method with a script.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By GeoSteven
      All files/tables in this discussion are fmp12 using FM 17 and both files are opened when initiating the app. "logging" is opened as the primary file and "claims" is opened as an external data source.
      I have a table called "newclaim" in "logging" which contains mailing address information including a USPS zip code. I have a table called "lu_ZipCode" in "claims". There is a relationship set between "newclaim" and "zipcode" on a field called "COID" (company ID). This field is automatically populated in both tables with "DCSI". This is an old method (used in Fm6) of establishing a simple relationship so that global fields can be used to pass parameters into scripts for zip code lookup and verification. 
      In the parent script (in "logging") I set the related field "lu_ZipCode::_gZipCode" with (for ex) "12345" with the intent of calling a subscript in "claims" then using "12345" to do the lookup. However when the subscript is called the "_gZipCode" field is empty. As a workaround I called the subscript passing the "12345" as a parameter which of course works as one may expect. After setting the parameter to a local variable in the subscript I'm able to do the lookup then populate the global fields "lu_ZipCode::_gCity" and "lu_ZipCode::_gState" while still in the "claims" file. Yep, sure enough when I go back to the parent file the global fields I just populated are empty. 
      It's worth pointing out here that prior to yesterday "zipcode" was a separate file and the methods described above worked like a charm. This effort is being put forth to consolidate this old FM6 solution with 40+ files into just a few files. I imported "zipcode" into the "claims" file then copied and modified the scripts anticipating that this would be a quick little project to get rid of one more file.
      I know there are better methods (ie JSON) to get this done and I will probably go that route. At the same time I'm left scratching my head on why this tried and true method doesn't work. Why would global fields I can populate from one side of the relationship not be populated when viewed from the other side? 
    • By amerioca
      Hi everybody,
       
      I recently amended a db from an old Lynda course, controlling shore excursions for river cruise ships. For my limited expertise (I am still a rookie at the very best) the construction is somewhat complex as there is many vessels, different routes, different charterers and consequently different shore excursion programs and prices. In some cases the shore excursion program of different vessels and operators is similar but not the same and so on.
      I have 7 different tables partly related (pls see graph attached):
       
      For every journey ::customers and ::trip are assigned a tour code and date (“tour code” is not unique as journeys repeat themselves during the season, “Tour Code” in conjunction with “Date” is unique)
      Every tour operator/vessel has many trips on every journey. All table occurences and fields are uniquely defined by unique id.
      Now I want to create an opening layout reflecting all relevant cruise information such as passenger list from ::customer and ::trip (shore excursions) for the specific journey, as well as other information in text fields.
      But I seem to be unable to get the right relationship and layout setups together. I tried all possible relations and layout setup combination but nothing worked so far.  
      On the same layout I was able to either retrieve the passenger list from ::customer for each journey or the offered shore excursion (trip) list from ::trip  but not both together on one layout.
       
      What is it, I am doing wrong? Any ideas are appreciated

    • By owangolama
      Here is my situation.
      I have a 25x25 grid - let's call it a map. Think of it like a game board, like Battleship or perhaps an Excel spreadsheet.
      I have a 9x9 view of this grid since it is not possible for a phone user to practically view 25x25, but 9x9 seems to work fine.
      And I have the concept of Current_Location on the map, which is dynamic and changes based on other factors.
      In TABLE1 I have records of all the "cells"  in the 25x25 map that hold information about the cell (for example, the color of the cell). There is other information stored here that includes, for example, whether you can "see" other cells from this particular location in the grid. So in the record for, say, cell B5, I have a field that houses a return-delimited list of the other cells that can be "seen" from this cell (B5 in this instance).
      Another table, TABLE2 has records that are also have a field for Current_Location, and it tells me which cells (of the 25x25 grid) should be displayed in the 9x9 grid. Usually, the Current_Location is the middle cell in the 9x9 grid, but if the Current_Location is on the edge of the 25x25 grid, then the 9x9 view is adjusted accordingly and the Current_Location is shown on the edge of the 9x9 grid. The user can also change their orientation so they can view the map from a different direction. The way I store this information is in a field that simply lists (return-delimited) all the cell that should be shown in the 9x9 grid. So in our previous example of B5, the record for B5 has a field called Grid that is a list of the 81 cells that should be shown in the 9x9 grid.
      For other reasons, I cannot combine these two tables even though they both are based on the concept of relating information to Current_Location. TABLE1 is actually not as simple as described, but I'm trying to limit the scope of my question.
      So here is my question: I have 81 (sigh) objects on my layout for this 9x9 grid. I need to access information in TABLE1 for each of the cells that are represented in the 9x9 grid.
      For example, let's again assume that my Current_Location is B5 and by using TABLE2 I know the names of all the cells shown in the 9x9 grid (they are stored in a list in a field in TABLE2). How do I then get information about each of those 81 cells out of TABLE1. I.e., each of the 81 cells should be colored according to information in TABLE1.
      So, for the top-left cell in the 9x9 grid (which appears as the FIRST item in the list field in TABLE2), I need to be able to look up the color in TABLE1, and use it to drive Conditional Formatting of the cell. So if Color(B5)=3, then make the background color of the cell green. I can grab the cell name, and could easily write a script to figure out the color, but I need to *look up* the color to use it in Conditional Formatting.
      I was trying to generate another field somehow that was an analogous list of 81 items that showed the color of each of the 81 cells (in the correct order), but 1) I couldn't figure out how to do that without running a script (which would take too long since these things are changing frequently and I really just need a data lookup); and 2) I think there must be a much cleverer way. I am still very new to join tables, but I think the answer is in that arena somewhere. The other idea I had was trying to write a Custom Function since I can easily grab the name of the cell, B5. But I don't know how to create such a function since it needs to access a particular table and it seems like functions don't really work that way.
      Thanks in advance for any help!!
    • By alanf
      I have a table named Sales with the following data:
      ----------
      ID
      DATE
      UNITS
      -----------
      Based on Sample data below, i am trying to query the table by creating a relationship to find the ID that achieved a specified Unit amount on the earliest date.
      In other words, and for example, which ID achieved a specified Unit amount first (earliest date)
      Specified Unit Amt of 5 = 1 Expected records returned result
      301 10/13/2018 6.5 Specified Unit Amt of 2 = 2 Expected records returned result
      766 10/03/2018 2 360 10/03/2018 2  
      I think a selfjoin may be required.  Any thoughts appreciated!
       
      Thanks
      Alan
       
      SAMPLE DATA
                                         ID                          DATE                                     UNITS
      301 10/15/2018 9 301 10/14/2018 8.5 301 10/13/2018 6.5 360 10/14/2018 5.5 305 10/15/2018 5.5 301 10/12/2018 4.5 656 10/13/2018 4.5 360 10/13/2018 4.5 305 10/14/2018 4.5 611 10/14/2018 4.5 305 10/13/2018 4 15 10/13/2018 4 301 10/11/2018 3.5 360 10/12/2018 3.5 611 10/13/2018 3.5 301 10/09/2018 3 15 10/11/2018 3 656 10/12/2018 3 611 10/12/2018 3 200 10/14/2018 3 301 10/06/2018 2.5 611 10/09/2018 2.5 200 10/13/2018 2.5 766 10/03/2018 2 360 10/03/2018 2 785 10/05/2018 2 301 10/05/2018 2 611 10/05/2018 2 439 10/10/2018 2 656 10/11/2018 2 510 10/15/2018 2 360 10/01/2018 1.5 510 10/04/2018 1.5 611 10/04/2018 1.5 305 10/05/2018 1.5 200 10/08/2018 1.5 656 10/09/2018 1.5 785 10/02/2018 1 766 10/02/2018 1 305 10/03/2018 1 439 10/03/2018 1 15 10/04/2018 1 301 10/04/2018 1 200 10/07/2018 1 656 10/08/2018 1 766 10/01/2018 0.5 510 10/01/2018 0.5 611 10/01/2018 0.5 200 10/06/2018 0.5
    • By Oyseka
      Hi All, I have a database where the relationship between the Quotes table and the QuoteItems table is set up such that if a record is deleted in the Quotes table, the related records in the QuoteItems table are also deleted. I have now been shown an instance on one Customer where there are four Quotes missing from the Quotes table but all the QuoteItems are still in the QuoteItems table. How can this be done, I am unable to recreate a circumstance where I can delete a quote and leave the quoteitems in place.
      Any help appreciated
×
×
  • Create New...

Important Information

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