Suggestions Please (select bulk rows and insert el...

appsheeter · 01-14-2022 05:16 PM

I’m building an app to manage a pipeline of loans a loan officer may be handling.

The relevant tables to this question are as follows.

TABLE RELATIONSHIPS

LOAN
LOAN ITEMS (LOAN is parent table, related by LOAN ID in this table)
LOAN ITEMS TEMPLATES (this table has no relationships.)

TABLE DESCRIPTIONS

LOAN (has all the relevant information about the loan: name, address, loan type, etc)
LOAN ITEMS (this has the checklists that are relevant to the LOAN its associated to.)
LOAN ITEMS TEMPLATES (This table holds a list of checklist items that are relevant to a given loan type)

LOAN ITEMS is basically an EAV table. We track status on each of these items. Its columns are like this…
ID, LOAN TYPE, CHECKLIST ITEM, STATUS, START DATE, END DATE

So one LOAN TYPE may have 50 entries in LOAN ITEMS TEMPLATES and another LOAN TYPE may have 70 entries in LOAN ITEMS TEMPLATES

PROBLEM STATEMENT

When I create a LOAN record, I then want to populate my LOAN ITEMS table with the relevent LOAN ITEMS TEMPLATES items based on the LOAN TYPE I selected when I created the loan.

PSEUDOCODE - Here is the logic I want to implement and I’m looking for the community to point me in the right direction. I can share my existing app if need be, right?

Click Default Items ACTION from LOAN card
Popup appears
select loan type of Purchase or REFI
- If Purchase select FHA or VA or CONV or USDA
  - select * from LOAN ITEMS TEMPLATES where FHA column = TRUE
  - select * from LOAN ITEMS TEMPLATES where VA column = TRUE
  - select * from LOAN ITEMS TEMPLATES where CONV column = TRUE
  - select * from LOAN ITEMS TEMPLATES where USDA column = TRUE
    - Now map fields from LOAN ITEMS TEMPLATES to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN ITEMS.ID
- If REFI
  - Select * from LOAN ITEMS TEMPLATES where REFI column = TRUE
    - Now map fields from LOAN ITEMS TEMPLATES to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN ITEMS.ID
Click Optional Items ACTION from LOAN card
Popup appears
Select loan type of Purchase or REFI
- If purchase then select * from LOAN ITEMS TEMPLATES where Optional – Purchase = TRUE
  - Now I want to be presented with these as rows in a table and I want to select them with checkboxes.
    - For the ones I selected, now map fields from LOAN ITEMS TEMPLATES to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN ITEMS.ID
- If REFI then select * from LOAN ITEMS TEMPLATES where Optional – REFI = TRUE
  - Now I want to be presented with these as rows in a table and I want to select them with checkboxes.
    - For the ones I selected, now map fields from LOAN ITEMS to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN.ID

Joseph_Seddik

Welcome to the community!

I’m having difficulties understanding your data structure. Could you please share your existing source sheet or a meaningful sample of it? Thanks.

appsheeter

the mapping during the process would be…

loan.ID → loan items.loan ID
loan items templates.Order → loan items.Order
loan items templates.Req → loan items.Req
loan items templates.Status → loan items.Status
loan items templates.Stage → loan items.Stage
loan items templates.Group1 → loan items.Group1
loan items templates.Group2 → loan items.Group2
loan items templates.Loan Item → loan items.Loan Item
loan items templates.Description → loan items.Description

Joseph_Seddik

This is what I had suspected actually, but didn’t want to jump to conclusions.

Your problem is here:

Source capture

In a database you should avoid having list items in columns. You are missing a Loan Types table, where you should list all your possible loan types.

In the Templates table, you should replace the type columns with a single column called “Loan Type”. This column should be of type Enumlist base ref, and will point to the “Loan Types” table. Now for each item in the Templates table you should be able to choose the corresponding Loan Types in the App’s Form, or enter them manually in the sheet separating the items with a “,”.

The loan items table should reference the Loans table, and also the Templates table that should serve as the base for all possible items of a loan.

Here’s your app:

appsheeter

Thank you for your response.

When I clicked on your link, it said “error cloning app”, so I did not get to see what you did, so keep that in mind.
Yes I understand that from a data modeling perspective it might seem incorrect that I have put each loan type as a column. I have done this intentionally. What I’m trying to do is make a templating system that is easy to modify by laymen, non-technical users. Many loans have the same loan items that need to be tracked. So I would like each item (Order Appraisal for example) to exist only once in the LOAN ITEMS TEMPLATES table. So when they want to add a trackable item that doesn’t exist, they just add to the LOAN ITEMS TEMPLATES table once and put an x or true or whatever in the appropriate type columns it applies to, then when they create a loan and select that loan type, everything with an x in that column is copied into the LOAN ITEMS table using the described mapping.

I hope I have clarified that the app never shows a screen of LOAN ITEMS TEMPLATES, except for maybe where optional items need to be selected as described in my original post in the 2nd popup. The app shows LOAN ITEMS.

I could put all loan types in a single column, however then there would be a minimum of 4 rows for “Order Appraisal” in LOAN ITEMS TEMPLATES. That would be fine for me, but a more confusing for users I think.

I did not get to see the app you put together, so I’m unclear if you handled the copying of rows from LOAN ITEMS TEMPLATES into the LOAN ITEMS TABLE.

Joseph_Seddik

Yeah, I just realized that and I don’t know why! The URL for the editor’s view eventually is redirected to the templates page. I hope other experienced members here can help @SkrOYC @Suvrutt_Gurjar please.

This is the URL in question: appsheet (dot) com /Template/AppDef?appName=CopiedeCopyofloan-5317163

Now in the meantime you can view the app here as a user, this one works:

I clearly see it as incorrect as impeding you from having a functioning app.

All this is already achieved, the only difference is that users will have the x checkboxes vertically in a multi-choice drop-down list.

Adding a dataset to the app and correctly modelling it in the datasource is one thing, and showing it to the user is another. You have to think about each one separately, as they are two independent things. You CAN control with a deep level of granularity what data is shown to what user, when, and if at all.

This is automatically handled by AppSheet through referencing.

It is not about being useful unfortunately, but at least personally I don’t know another way to do it.

You’ll still be able to hardcode them if so you desire, there is no restriction here.

I’m sorry, this is quite frustrating. I hope the more experienced friends I’ve mentioned can help us in this regard.

appsheeter

Ok, I see you created the LOAN TYPES table and related it to LOAN ITEMS TEMPLATE.

I created a new loan, I gave it Nate Test as Lead ID. You can see it has not LOAN ITEMS associated to it right now.

How would I get an action to take all the records from LOAN ITEMS TEMPLATE with VA in the loan type column copied into the LOAN ITEMS table for the new loan I created?

Joseph_Seddik

Please read this excellent guide:

appsheeter

I haven’t fully investigated that post yet, but it looks like I should just use the AppSheet API to select records, map them and push them to another table. Or possibly just push the required logic to database or google sheets via API.

Steve

“At all” looks to be more accurate.

Joseph_Seddik

It says:

appsheeter

I am following you that a loan types table would be useful for storing the types to use in logic rather than hard coding those types.

But I couldn’t see your project, so I’m unclear how you used it for the solution.