Hi all sorry for any noob questions, I'm fairly new to FM PRO,Ive been reading watching videos but I'm stuck right now, what I'm trying to do is a loan DB i have only 4 layers right now (Customers - Loans - Loan List - Payment).

I'm able to create the customers and create the loans I can even view my loan list, my problem is in the payment, what i want to do is from the loan list click a customer and load that loan in the payment layer so i can make payments to that customer i will upload my file for you to see, sorry if you see errors I'm really new to this, any help would me appreciated.

Thank you

Chino DB.zip

Hi LoboX, welcome to FM Forums!!

Here is the basic concept ...

You are on Loans (PRESTAMOS) and you want to go to Payments and add a new record. So you must grab the necessary IDs (in this case you want the PRESTAMOS::__pkID and also the PRESTAMOS::_fKCLIENTEID) and then you then go to a layout based upon Payments (PAGOS) and you create a new payment record and insert the IDs into the new record.

So place a button on the PERSTAMOS List layout and script attached might be similar to this:

Set Variable [ $loanID ; PRESTAMOS::__pkID ]

Set Variable [ $clientID ; PRESTAMOS::_fKCLIENTEID ]

Go to Layout [ data-entry layout based upon PREGOS ]

New Record/Request

Set Field [ PREGOS::_fkPRESTAMOID ; $loanID ]

Set Field [ PREGOS::_fKCLIENTEID ; $clientID ]

One additional suggestion I will make ... I believe you will find it confusing if you name your primary keys with simply ID or __pkID. It is because, when using in situations such as this script, how do you know what table __pkID belongs to? All relational keys should be named to clearly identify the table they represent, such as __pkPrestamosID. What if, in this example, you needed to grab the primary key from an auxiliary table and also Loans? You would have two _pkIDs. How will you tell them apart? In the above script, I named the variables more clearly but I think you can see the potential issues with your naming.

If this script is not what you need, or you get stuck, just let us know. There are many wonderful folks here who are willing to assist you. :yep:

Thank you very much for the reply, i will try what you said and i will fix the names of the primary keys, i will keep you updated if i get it working.

Cheers

A correction: You usually will only need the LoanID in Payments. The ClientID is not really necessary. However, many times when you get deeper in a table occurrence group, adding the 'primary entity's ID' can be beneficial and it takes up little space. LineItems are prime example where a ClientID (although unnecessary since it can be displayed through the relationship) can have many uses in LineItems.

In truth, you do not need to grab the ClientID in the above script. You can set the ClientID in Payments to be a lookup (or auto-enter calculation) ... pulling its value from Loans.

Hi before posting any info, I'm trying everything i can to make this work with what you told me, one thing PREGOS = PAGOS right? what is usually happening is when i click the button it jumps to the PAYMENT layer and creates a new record but it doesn't show any info regarding the Loan i clicked.

Yes, sorry, I had to look up and translate to get the meanings of the tables ... Pregos is a spaghetti sauce, LOL.

I notice that both your foreign keys in Payments (the ID for Clients and Loans) is of type text. You should change those to number. It still should work right so I created the script for you. Please activate the button on the Loans layout in this revised file and then see how your script differs from it.

Chino DBREV.zip

Hehe yeah i love Pregos . LaRetta thanks a lot for the script i almost just almost did it myself i was so close, when i open the file that you send me right now i was like WHAT!!! just missed 2 things in my script, but really thanks a lot i understand more now and i can continue building this DB, lets hope i don't get stuck, but thanks again.

Cheers :laugh:

:waytogo:

Any time.